Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

Getting date to show as it is in scatter chart

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
agwalsh
Asked:
agwalsh
  • 2
  • 2
2 Solutions
 
Rgonzo1971Commented:
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
 
5teveoCommented:
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
 
agwalshAuthor Commented:
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
 
agwalshAuthor Commented:
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
 
5teveoCommented:
thanks for points!
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now