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

By Ali, Founder, Excel Lessons

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.

Read more about this

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. TEXT shows a number in any format.

In C2, type:

=TEXT(B2,"d mmmm yyyy")

B2 holds the number 1.

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. TEXT shows a number in any format. In C2, type: =TEXT(B2,"d mmmm yyyy"). B2 holds the number 1.

    Hint. The format goes in quotes.

  2. 2

    In C4, show B4 as a date with TEXT. Shape: =TEXT(cell,"text"). Use day, month name and a four-digit year.

    Hint. The format goes in quotes.

  3. 3

    Because a date is a number, ordinary arithmetic works on it. In C5, subtract B4 from B5. That is the gap 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

    Read =B3-B2 and say how many days that is. B2 is day one, B3 the year 2000.

    Hint. Both cells are already serials.