Fix Google Sheets Date Values Appearing as Numbers (44562) or Wrong Format

What's Going On

You paste a column of dates into Google Sheets and they turn into numbers like 44562, 45012, or similar five-digit integers. Or you write a TEXT() formula to format a date and get back something completely unexpected — wrong month, wrong separator, or the format string echoed back verbatim.

Almost everyone hits this at some point. The root cause: Google Sheets stores every date as a serial number — days counted from December 30, 1899. So 44562 is January 1, 2022. A cell only shows a human-readable date when the right format is applied. Remove that format — or never set it — and you see the raw integer.

The exact error condition looks like this:

Date values appear as numbers (e.g., 44562) instead of formatted dates, or the TEXT function returns an unexpected date format.

Diagnosing the Problem

Start by identifying which of three scenarios you're dealing with — the fix is different for each.

Scenario 1 — Cell shows a raw number

Click the cell showing 44562. Check the format dropdown in the toolbar (it usually reads "Automatic" or "Number"). If it shows Number instead of Date, the cell lost its date formatting. The underlying value is still a valid date serial — it just needs the right display format.

Quick check: type =A1+0 in another cell and format that cell as a date. If you see a real date like Jan 1, 2022, the data is fine. Only the display is broken.

Scenario 2 — TEXT() returns the wrong format

You write something like:

=TEXT(A1, "MM/DD/YYYY")

And get 01/15/2022 when you expected 15/01/2022. Or you get #VALUE!. Or the output format just doesn't match what the documentation says it should.

Nine times out of ten, this is a locale mismatch. The format codes inside TEXT() are locale-sensitive in Google Sheets — the same string behaves differently depending on your spreadsheet's region setting.

Scenario 3 — Imported or pasted data shows numbers

You pulled in a CSV or copied data from another app, and dates arrived as raw integers. Sheets had no format metadata to read — it can't tell 44562 is a date rather than a quantity or ID. So it leaves it as a number.

Fixing Raw Numbers That Should Be Dates

Method 1 — Apply a date format via the menu

• Select the cells showing raw numbers.
• Go to Format → Number → Date (or Date time if you also need time).
• The numbers should immediately render as dates.

Need a specific format like YYYY-MM-DD? Go to Format → Number → Custom date and time and define it there.

Method 2 — Custom number format

Select the cells, open Format → Number → Custom number format, and enter your pattern:

YYYY-MM-DD          → 2022-01-15
DD/MM/YYYY          → 15/01/2022
MMM DD, YYYY        → Jan 15, 2022
DD-MMM-YY           → 15-Jan-22

Click Apply, then verify the cells show readable dates.

Method 3 — Force conversion with a formula

Formatting alone won't work if the cells contain text strings that look like numbers rather than actual numeric values. Convert them first:

=DATEVALUE(TEXT(A1,"0"))

Or, if the serial numbers are stored as text and you just need them numeric:

=A1*1

Then format the result column as a date.

Fixing TEXT() Format Mismatches

The TEXT() function reads format codes through the lens of the spreadsheet's locale. Set to France? Your date separator might be a period. Set to Japan? Month formatting behaves differently than in English. The fix is to either align the locale with your expectations or use format codes that sidestep the issue entirely.

Check and fix the spreadsheet locale

• Go to File → Settings.
• Under the General tab, check the Locale field.
• Change it to match your expected date format conventions.
• Click Save settings.

After changing locale, re-evaluate your TEXT() formulas — output will reflect the new setting.

Use locale-agnostic format codes

Better yet, write formulas that behave the same regardless of locale:

=TEXT(A1, "YYYY-MM-DD")     → ISO 8601, consistent everywhere
=TEXT(A1, "DD/MM/YYYY")     → Day-first format
=TEXT(A1, "MM/DD/YYYY")     → US format

Stay away from abbreviated month names in non-English locales unless you've confirmed how they render.

Skip TEXT() and build the string manually

When TEXT() keeps misbehaving, ditch it. Use DAY(), MONTH(), and YEAR() directly:

=YEAR(A1)&"-"&TEXT(MONTH(A1),"00")&"-"&TEXT(DAY(A1),"00")

This always produces YYYY-MM-DD — no locale involved, because you're constructing the string yourself from parts.

Fixing Imported Dates That Lost Their Format

When a CSV brings in dates as raw serial numbers, reconstruct the original date by adding the serial to the base epoch:

=DATE(1899,12,30) + A1

Google Sheets serial day 1 = January 1, 1900. Day 0 = December 30, 1899 (an artifact of the Lotus 1-2-3 compatibility bug). Adding your serial to that base date recovers the correct date.

Format the result column as a date. Then to replace the originals with values — not formulas:

• Copy the formula column.
• Paste as Values only (Ctrl+Shift+V) into the original column.
• Format as date.
• Delete the helper formula column.

Verifying the Fix

Before closing the spreadsheet, run a quick sanity check:

• Cells display a human-readable date, not a five-digit integer.
• Click a fixed cell — the formula bar should show the date in your locale's format (e.g., 1/15/2022), not a raw number.
• Run =ISNUMBER(A1) on the cell — it should return TRUE. Dates in Sheets are stored as numbers, so this confirms the underlying value is valid.
• Run =ISDATE(A1) — returns TRUE if Sheets recognizes the cell as a date type.
• If you used TEXT(), verify the output string matches your expected format exactly.

Preventing This in the Future

Three habits that stop this problem before it starts:

• Lock your locale before importing data. Set it under File → Settings before any import or paste operation. Changing it after the fact can shift how existing values display.
• Use ISO 8601 in source data. Dates formatted as YYYY-MM-DD parse unambiguously in virtually every locale. If you control the export format, use this.
• Double-check after migrating from Excel. Excel and Sheets share the same serial date system, but Excel has a 1900 leap year bug that can cause a one-day offset on dates before March 1, 1900. Always spot-check a few dates after migration.
• Reserve TEXT() for display only. For date arithmetic, reach for DATEDIF(), DATE(), or EDATE(). Use TEXT() only at the end of a formula chain when you need a formatted string for output.