Secrets of Excel dates

 Jan 12, 2017

It’s not meant to be a secret, but do you know what Excel really stores dates as?

I’ll give you a clue, have you ever typed a date into Excel and it turned into a number, or a number into Excel and it turned into a date?

You got it, Excel stores dates as numbers. So even though we see them in the grid as dates or even in the formula bar as dates, Excel sees them differently. Excel stores them as the number of days since a starting date. Want to hazard a guess as to the starting date?

I’ll give you a clue, New Year’s Day year 2000 is 36,526 days since this date.

If you guessed the 1st of January 1900, you would be right. That’s day 1.

So Christmas 2016 is 42,729 days since the 1/1/1900.

So when people type in a date and turns into a number (or vice versa), it isn’t wrong, it’s simply that that cell already has a format on it and Excel is showing the date as it sees it.

Now, here’s a fun exercise, create a new blank worksheet.

Click on cell J1 and press Ctrl + ;, then press enter to finish editing the cell.

Note: Ctrl + ; inserts today’s date, based on the computer’s clock. It’s not a formula or anything fancy, but it is quicker than typing the date yourself.

Widen the column width, notice how the date sits on the right hand side of the cell. That’s a big clue! Excel’s natural inclination is to store text on the left hand side and numbers on the right hand side of the cell. Dates ARE numbers so that’s why the date is on the right hand side.

Click back on J1 and in the Home tab format the cell back to General format or Number format.

Notice how it gives a number, around 42-43K, now you know why.

Format J1 back to Short Date format, it goes back to the date you typed earlier.

Why does Excel store dates as numbers? Because it’s more efficient for computers to store numbers rather than text.

Can we make use of this? YES!

Type in the date for the next Christmas day in cell J2. Type it in this format 25/12/20XX (replace the XX with the current year). Go to J3 and type:

=J2-J1

They are both really numbers, Christmas day is a bigger number, to work out the difference between two numbers you subtract one from the other.

You now know how many shopping days till Christmas!

Note: This only works with dates after 1/1/1900, it doesn’t store 25/12/2020 as a negative number, it doesn’t even store it as a date at all (it stores it as a piece of text).

To explore Excel dates and date formulas, take a look at our Excel Level 2 training course.


 

How do your Excel skills stack up?   

Test Now  

About the Author:

Matthew Goodall  

Matthew is a qualified Microsoft Office Specialist, Microsoft Certified Applications Specialist and a Microsoft Certified Trainer with over 11 years of hands-on experience in a training facilitation role. He is one of New Horizons most dynamic instructors who consistently receives high feedback scores from students. Matt enjoys helping students achieve real professional and personal growth through the courses he delivers. He is best known for creating “fans” of students, who regularly request him as an instructor for any future courses they undertake at New Horizons.

Read full bio
top