Level 5 · Why a date shows as 45672

Every date puzzle in a spreadsheet comes back to one fact: a date is a number with a format on top. Once you see that, a cell full of five-digit numbers stops looking like broken data.

Days since 1900

Excel counts days from the start of 1900. Day 1 is January 1, 1900; day 45672 is January 15, 2025. Google Sheets uses the same numbers for every modern date. A time is a fraction of a day, so 45672.75 is 2025-01-15 at 6 pm.

You see	The cell holds
2025-01-15	45672
2025-01-16	45673
2025-01-15 18:00	45672.75

That is why =B2+7 is a week later, and why subtracting two dates gives days.

Value vs format

What a cell holds and what it shows are separate. The format decides the display: yyyy-mm-dd, m/d/yyyy, mmm d, or General, which shows the raw number. Paste values into a new column, import a CSV, or build a pivot, and the format can be left behind while the value stays. The fix is formatting, never retyping: select the cells, press Ctrl+1 (Cmd+1 on a Mac), and pick a date format. In Google Sheets it is Format, Number, Date.

The opposite trap: TEXT returns text

=TEXT(B2,"yyyy-mm-dd") returns the characters 2025-01-15. It looks like a date, but it is text: it sits on the left of the cell, SUM ignores it, sorting a column of "mmm d" labels puts April before January, and a filter for “dates after” skips it. TEXT is right for labels and month keys, and wrong as a way to “fix” a date that shows as a number.

How to tell what you have

• Alignment. With default formatting, numbers (and so dates) sit on the right, text on the left.
• =ISNUMBER(B2). TRUE for a real date, FALSE for text that looks like one.
• General format. A real date turns into a number like 45672. Text that looks like a date doesn’t change.

Common mistakes

• Retyping or re-exporting dates that only lost their format.
• Using TEXT to “repair” a number into a date: the result is text.
• Treating 45672 as an id or a quantity in a report: if it sits in a date column and lands between 40000 and 47000, it is almost certainly a date from 2009 to 2028.