Excel Dates’ Logic

On a windows PC, excel stores dates as the number of days since January 1, 1900. For a date such as 9/15/2007, excel actually stores the value 39,340, but it formats the date to show you a value such as 9/15/2007.

Try this: –

1. Enter the number 1 in cell A1.

2. Select cell A1. Go to format cells dialog box. Change the format to date. Result will be Jan 1, 1900.

3. Similarly, for 2 it will be Jan 2, 1900, and so on.

So, some of us always thought that excel dates when come in numbers are some sort of ASCI values, but they actually aren’t!


The DATEVALUE function converts text that looks like a date into the equivalent serial number (as explained above). You can then use the format cells dialog box to change the format to date and display as a real date. This real date can then be used properly in charts as well.