Getting date to show as it is in scatter chart

Posted on 2014-01-10
Medium Priority
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. :-)
Question by:agwalsh
  • 2
  • 2
LVL 54

Accepted Solution

Rgonzo1971 earned 1400 total points
ID: 39770763

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


Assisted Solution

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

this formula is repeater in new column to reformat text date.

new format will be mm/dd/yyyy

Use this new date column to pivot against.

Author Closing Comment

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.

Author Comment

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 :-)

Expert Comment

ID: 39776813
thanks for points!

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

If Skype for Business came with your office 2016 or office 365 installation, you may find that it's almost impossible to either disable or remove it. The application will often launch with each start of Windows, even when explicitly configured not t…
I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. U…

624 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