Convert text dates to real dates to allow the date-based axis to work.
Transcript of the video:
MrExcel podcast is sponsored by Excel-XL.
Excel Charts Chapter 3; Date Value.
Oh hey, welcome back to MrExcel netcast. I'm Bill Jelen.
Today I build a Chart from this data, it has 4 points in time, the 1st of January, the 1st of 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, you can always hold down Ctrl and press the backwards accent that the Grob accent from French and if the dates don't change to a number when you do that, then you know that you have text date.
So I want to try and convert those to real dates.
I'm going to use equal DateValue... of that text that looks like a date.
And what we should see is, 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, you gonna format cells Ctrl+1.
Choose a nice date format, again go back to the one that looks just like what I had before, copy that and then paste his values, paste... values, actually I want, I use N number format.
All right and then now I have real dates, you can tell because I can press Ctrl and the backwards accent to 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, it has opposed to February 1st 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 that you choose the date axis or even automatically work but it requires the dates to be true dates not text dates.
Hey, I wanna thank you for stopping by.
We'll see you next time from MrExcel.
Excel Charts Chapter 3; Date Value.
Oh hey, welcome back to MrExcel netcast. I'm Bill Jelen.
Today I build a Chart from this data, it has 4 points in time, the 1st of January, the 1st of 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, you can always hold down Ctrl and press the backwards accent that the Grob accent from French and if the dates don't change to a number when you do that, then you know that you have text date.
So I want to try and convert those to real dates.
I'm going to use equal DateValue... of that text that looks like a date.
And what we should see is, 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, you gonna format cells Ctrl+1.
Choose a nice date format, again go back to the one that looks just like what I had before, copy that and then paste his values, paste... values, actually I want, I use N number format.
All right and then now I have real dates, you can tell because I can press Ctrl and the backwards accent to 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, it has opposed to February 1st 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 that you choose the date axis or even automatically work but it requires the dates to be true dates not text dates.
Hey, I wanna thank you for stopping by.
We'll see you next time from MrExcel.
