Excel Charts 3 - Converting to Dates

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Jul 16, 2010.
Convert text dates to real dates to allow the date-based axis to work.
maxresdefault.jpg


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.
 

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top