Why Your Spreadsheet Dates Turn Into Numbers

Your date didn't break. It was never a date.

A spreadsheet stores 15 March 2026 as the number 46096 and keeps nothing else about it. The date you normally see is a costume the cell wears — a number format painted on top of that integer. Take the format away and the number underneath shows through.

So the fix is nearly always three seconds of work: select the cells, apply a date format, done. No data is lost when this happens — the value is intact, only its clothing changed. What's worth understanding is the counting system underneath, because it explains every weird symptom that follows: the dates that arrive four years early from a colleague's file, the timestamps that lose their time, the product code 1-10 that turns itself into January 10th.

A date is a day count, not a date

Spreadsheets don't store years, months and days. They store a serial number: how many days have passed since a fixed zero point. Excel and Google Sheets share that zero point, so the same serial means the same date in both.

This design is why date maths in a spreadsheet works at all. Subtract one date cell from another and you're subtracting two integers, so the answer — the number of days between them — comes out for free. Sorting a date column is sorting integers. Adding 30 days is adding 30. The calendar's irregularities are already baked into the count.

It's also why the display is fragile. The number is the truth; the format is a preference, and preferences get overwritten.

Where day 1 actually sits

Here's the quirk that trips up anyone converting serials outside the spreadsheet. Serial 1 is 1 January 1900, but the count includes 29 February 1900 — a day that never existed. 1900 was not a leap year (century years must be divisible by 400, and 1900 isn't), yet the serial system reserves number 60 for it. The bug was inherited deliberately from an earlier spreadsheet program for file compatibility, and it has never been removed.

The practical consequence: for every date from 1 March 1900 onward — which is to say, everything you will ever work with — the count behaves as if day zero were 30 December 1899. That's the rule to use when you move a serial into another system:

real date = 1899-12-30 + (serial number) days

Check it against the table: 25569 days after 30 December 1899 is 1 January 1970, and 45000 lands exactly on 15 March 2023.

Only dates before 1 March 1900 are off by one, and Excel can't store dates before 1 January 1900 as dates at all — type an earlier one and it stays plain text. The ceiling is serial 2958465, which is 31 December 9999.

Times are the decimal part

If your value came back as 46096.395833 rather than a clean integer, the fraction is the time of day, expressed as a fraction of one 24-hour day.

09:30 is 9.5 hours ÷ 24 = 0.395833 (rounded to 6 dp), so 46096.395833 is 15 March 2026 at 09:30. This also explains a common complaint: a date-and-time value pasted into a cell formatted as a plain date keeps its fraction but stops showing it, and a value rounded to a whole number loses the time permanently.

The five ways the format gets stripped

Every case is the same event — the number format reverting to General — arriving by a different route.

Pasting values into a General cell. Copy dates, then Paste Special → Values into a column that was never formatted as dates, and you paste integers into cells with no instruction to display them as dates. Paste normally (format included), or format the destination first.

Clearing formats. Clear → Clear Formats and most "tidy up this sheet" routines wipe the number format along with the fill colours. It's the most common self-inflicted version.

A formula returning a date. VLOOKUP, XLOOKUP, INDEX, IF, MIN, MAX and friends return the underlying value, not the source cell's appearance. Where the formula cell is General, you get the serial. Format the formula cell as a date.

Importing a CSV. A CSV holds text, so the import step has to guess. It may read 03/15/2026 as a date, as text, or — if the file was exported from a General-formatted sheet — as the literal serial number, in which case you receive integers that were never labelled as dates by anyone.

Joining text. =A1 & " deadline" or CONCAT/TEXTJOIN converts the date to its raw number first, producing "46096 deadline". Wrap it: =TEXT(A1,"yyyy-mm-dd") & " deadline" gives "2026-03-15 deadline".

Getting the date back

1. Select the cells.
2. Excel: Home → the Number Format dropdown → Short Date or Long Date. The keyboard shortcut Ctrl+Shift+# applies a date format directly. Google Sheets: Format → Number → Date.
3. If you want a specific layout, use Custom / Custom date and time — yyyy-mm-dd is the safe machine-readable choice.

If the dates come back correctly, you're finished; the value was fine all along.

If applying a date format changes nothing and the value stays left-aligned as typed, you have the opposite problem: it isn't a number at all, it's text that looks like a date. Dates stored as text won't sort chronologically, won't subtract, and won't respond to number formatting. Convert them with DATEVALUE, or select the column and run Data → Text to Columns → Finish, which forces a re-parse. Watch what happens to alignment as a diagnostic: real dates and numbers sit right-aligned by default, text sits left.

The reverse problem: numbers that become dates

The same machinery runs backwards. Type 1-10, 3/4 or Mar-5 and the spreadsheet recognises a date pattern, converts your entry to a serial, and applies a date format — so a part number becomes 10 January and a fraction becomes 4 March. It has happened to enough scientific data that the body responsible for human gene naming renamed several gene symbols specifically because spreadsheets kept turning them into dates.

To stop it, format the cells as Text before entering anything, or prefix a single apostrophe ('1-10), which forces text entry and doesn't appear in the cell. Fixing it afterwards is much harder: once 1-10 has been swallowed into a serial number, the original characters are gone and only re-entry brings them back.

When a serial leaves the spreadsheet

Serials travel badly, and there are three specific traps.

The 1904 date system. Excel supports a second zero point, historically the default on older Mac versions, where the count starts at 1 January 1904. Every date in a 1904-system file is 1462 days — four years and a day — away from the same date in the 1900 system. Paste dates between files using different systems and everything shifts by that amount. It's a per-file setting, not a per-cell one; if a whole column arrived four years out, check it before you start editing values by hand.

Unix timestamps. Serials are days from 1899-12-30; Unix time is seconds from 1970-01-01 UTC. To go from one to the other: unix = (serial − 25569) × 86400, and back with serial = unix ÷ 86400 + 25569. Two conversions live in that formula, so it's worth checking the result against a known date the first time. If you're unsure which system your number belongs to, digit count settles it: a modern spreadsheet serial is five digits, a modern Unix timestamp in seconds is ten, and in milliseconds thirteen. The full picture of epochs, offsets and what each system is actually counting is in our guide to how computers count time.

Timezones. A spreadsheet serial carries no timezone. It's a wall-clock reading with no offset attached, so nothing in the file records whether 09:30 meant London or Los Angeles. When you convert serials to timestamps for a database or an API, you supply the timezone assumption yourself — and if you assume wrong, every value shifts by a few hours without a single visible error.

Frequently Asked Questions

Why does my Excel date turn into a number like 46096? Because that's what the cell always contained. 46096 is the count of days from the spreadsheet's zero point to 15 March 2026, and the readable date was a number format sitting on top of it. Something reset the format to General. Reapply a date format and the date returns.

Did I lose my dates? No. The serial number is the complete value — formatting it as a date is fully reversible, with no loss of information. The only genuinely destructive cases are rounding a date-and-time value to a whole number, which discards the time, and a number that was auto-converted to a date on entry.

How do I convert a serial number to a real date outside a spreadsheet? Add the serial to 30 December 1899. That rule is exact for every date from 1 March 1900 onward; before that, the phantom 29 February 1900 in the serial count puts you one day out.

Why are the dates in a shared file four years off? The file is almost certainly using the 1904 date system while yours uses 1900, or the reverse. The gap is exactly 1462 days. Fix it at the file-setting level rather than adjusting individual cells.

Why does formatting as a date do nothing to my column? The values are text, not numbers, so there's no number for the format to style. Left alignment is the tell. Run Text to Columns over the column, or convert with DATEVALUE, then apply the date format.

Format is the costume, the number is the person

Once you see a date as an integer with a display rule attached, every one of these symptoms stops being mysterious. The date maths that just works, the four-year shift, the time that vanishes, the gene name that became a Tuesday — all one mechanism, seen from different angles.

Got a bare serial number and no spreadsheet in front of you? Medley Web is building a free hub of instant, in-browser converters, date and time tools among them. Decode a date or timestamp free at medley-web.com.