In Episode #1284, Bill looks at Charting Dated Data and how to get a more realistic distance between dates. Included in Podcast Episode #1284: Using =DATEVALUE(). "Charts And Graphs: Microsoft Excel 2010", by Bill Jelen
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Excel Charts, Chapter 3, Date Value.
Oh! Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today, I build a chart from this data it has four points in time the first two January the first in February and then a whole lot of time goes by 15th of July and 29th of July trying to show how these readings changed over time, but what frustrates me is the axis down here is showing each of these points as equidistant and that's clearly not true.
So, I tried right clicking and choosing Format Axis and changed it to a Date axis, but that didn't fix anything.
So, then I said, all right, well you know, what's going on here why isn't this working and I went and looked at the dates, I see that the dates are stored as text.
Now, if you can't tell, you can always hold down Control and press the backwards accent, that the Grave accent from French and if the dates don't change to a number when you do that then you know, that you haven't text date.
So, I want to try and convert those to real dates.
I'm going to use equal DATEVALUE, DATEVALUE of that text that looks like a date, we should see as we get a number.
All right, that's perfect that's the number, I copy that number down.
All right and then format that, so when you're going to Format Cells, Control+1 choose a nice Date format, I can go back to the one that looks just like what I had before and we will copy that and then paste as values.
Paste, Paste Values, actually I want Values & Number Format.
All right and baam, obviously now, I have real dates you can tell because I can press Control and the backwards accent get to see the number, press it again and you can see that, now this chart is showing that the distance from January 1st to February 1st is pretty narrow as opposed to February 1st to out here to somewhere in July.
All right! So, we're actually getting a true date picture showing the gap between the dates.
Again to get here right click, Format Axis, you want to make sure to choose the Date Axis or even Automatically and it work, but it requires the dates to be true dates and not text dates.
See you next time for another netcast from MrExcel.
Excel Charts, Chapter 3, Date Value.
Oh! Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today, I build a chart from this data it has four points in time the first two January the first in February and then a whole lot of time goes by 15th of July and 29th of July trying to show how these readings changed over time, but what frustrates me is the axis down here is showing each of these points as equidistant and that's clearly not true.
So, I tried right clicking and choosing Format Axis and changed it to a Date axis, but that didn't fix anything.
So, then I said, all right, well you know, what's going on here why isn't this working and I went and looked at the dates, I see that the dates are stored as text.
Now, if you can't tell, you can always hold down Control and press the backwards accent, that the Grave accent from French and if the dates don't change to a number when you do that then you know, that you haven't text date.
So, I want to try and convert those to real dates.
I'm going to use equal DATEVALUE, DATEVALUE of that text that looks like a date, we should see as we get a number.
All right, that's perfect that's the number, I copy that number down.
All right and then format that, so when you're going to Format Cells, Control+1 choose a nice Date format, I can go back to the one that looks just like what I had before and we will copy that and then paste as values.
Paste, Paste Values, actually I want Values & Number Format.
All right and baam, obviously now, I have real dates you can tell because I can press Control and the backwards accent get to see the number, press it again and you can see that, now this chart is showing that the distance from January 1st to February 1st is pretty narrow as opposed to February 1st to out here to somewhere in July.
All right! So, we're actually getting a true date picture showing the gap between the dates.
Again to get here right click, Format Axis, you want to make sure to choose the Date Axis or even Automatically and it work, but it requires the dates to be true dates and not text dates.
See you next time for another netcast from MrExcel.
