Excel Practice
Lessons Lesson 73

How Excel Stores Dates

About this lesson

Excel stores a date as a plain number. It counts the days since the start of 1900. The date you see on screen is a format laid over that number.

By the end you can

  • say what a date really is in Excel and why arithmetic works on it.
  • show a serial number as a readable date with TEXT.

Practises TEXT DATE

The idea

Serial 1 is 1 January 1900. Serial 45000 is a day in March 2023. The cell contains the number and nothing else. The day, month and year are a way of displaying it. Change the format and the number is unchanged. That is why a column that suddenly shows five-digit numbers has not lost anything.

Almost every date problem people have comes from not knowing this. Subtracting one date from another gives the days between them, because it is a subtraction of two counts and nothing more. Adding seven gives a week later, across a month end, without a function. A date compared with > sorts correctly, because numbers do. And a date that arrives from another system as text will do none of those things. It looks the same on screen. That is the other half of the same fact, and the reason VALUE exists.

The mistake to watch for

Treating a five-digit number in a date column as lost data. The cell always held that number. A format was showing it as a date, and the format has gone. The opposite fault is the expensive one. A date that arrived from another system as text looks the same on screen. It will not subtract, compare or sort. VALUE or DATE is what turns it back into a number.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

Excel stores a date as a plain number. It counts the days from the beginning of 1900. TEXT shows a number in any format you write, so it can show a serial as the date it is. B2 holds the number 1.

In C2, type =TEXT(B2,"d mmmm yyyy") to see which day number 1 is.

C2
Row A B C D E F G
1 Event Serial Shown as
2 Day one 1
3 Millennium 36526
4 Order placed 45000
5 Delivered 45014
6
7 Days in a week 7
8

Every step

  1. 1

    Excel stores a date as a plain number. It counts the days from the beginning of 1900. TEXT shows a number in any format you write, so it can show a serial as the date it is. B2 holds the number 1. In C2, type =TEXT(B2,"d mmmm yyyy") to see which day number 1 is.

    Hint. The format goes in quotes.

  2. 2

    Show the order date serial in B4 as a readable date in C4. Day, month name and four-digit year.

    Hint. The format goes in quotes.

  3. 3

    Because a date is a number, ordinary arithmetic works on it. In C5, work out how many days there were between the order and the delivery.

    Hint. One minus the other.

  4. 4

    Adding works too. Read =TEXT(B4+B7,"d mmm") and say what date is a week after the order.

    Hint. Seven days on.

  5. 5

    One more to make it concrete. Read =B3-B2 and say how many days there are between day one and the start of the year 2000.

    Hint. Both cells are already serials.