Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Getting date to show as it is in scatter chart

Posted on 2014-01-10
5
Medium Priority
?
239 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 1400 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 600 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

719 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