Solved

Getting date to show as it is in scatter chart

Posted on 2014-01-10
5
228 Views
Last Modified: 2014-01-13
Set up a pivot table to summarize data and then to get a scatter chart out of it I copied and pasted it as values. So far so good...
However I have brought in the dates, tweaked them and my Revised Date column is looking like a date but alas is not quacking like the proper date in the scatter chart I created from it. The dates are all showing up as 1900 dates instead of the dates as given...even though the dates look like dates. What am I missing here. (Scatter chart is right at end of the data)

Thanks as always. :-)
Scatterchart-not-showing-dates.xlsm
0
Comment
Question by:agwalsh
  • 2
  • 2
5 Comments
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 350 total points
ID: 39770763
Hi,

The dates that you have are not recognized as Dates that's where the problem is

EDIT to change them to Dates insert 0 (Zero) in a cell otherwise nonused

And then PasteSpecial / Operation Add

Maybe you will have to reformat the cells as Date

Regards
Scatterchart-not-showing-datesV1.xlsm
0
 
LVL 8

Assisted Solution

by:5teveo
5teveo earned 150 total points
ID: 39771726
Reformat your date using new forumula

this formula is repeater in new column to reformat text date.
=DATE(RIGHT(E2,4),MID(E2,FIND("/",E2)+1,2),LEFT(E2,2))

new format will be mm/dd/yyyy

Use this new date column to pivot against.
0
 

Author Closing Comment

by:agwalsh
ID: 39776230
Rgonzo1971 - sigh...worked so well. Thank you :-) Did the job..
Steveo - I tried your formula but got Value message all the way down..but still appreciate the effort.
0
 

Author Comment

by:agwalsh
ID: 39776240
Just to say the reason I allocated the points in this way is that while Rgonzo1971 solved my problem...I also appreciated Steveo's formula and effort :-)
0
 
LVL 8

Expert Comment

by:5teveo
ID: 39776813
thanks for points!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question