Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
Fix the Serial number error
Excel & Google SheetsWhat Serial number means
Spreadsheet applications store every date as an integer — the number of days since a fixed start date (January 0, 1900 in Excel; December 30, 1899 in Google Sheets). When a cell's format is set to General or Number, this underlying serial number is displayed instead of the formatted date.
Common causes
- A formula that returns a date (such as TODAY() or DATE()) is placed in a cell formatted as General or Number, so the serial number is shown instead of the date.
- A date value was pasted into a column formatted as Number, stripping the date format.
- The cell format was manually changed from Date to Number or General after the date was entered.
- DATEDIF, NETWORKDAYS, or another date function result is formatted as a date when it should be a plain number — or vice versa.
- A date was returned from an external data connection or formula, and the destination cell inherited a numeric format from a neighbouring cell.
Example fix
Broken
=TODAY()Fixed
=TEXT(TODAY(),"DD/MM/YYYY")If TODAY() is in a General-formatted cell it may show as a serial number. Wrapping in TEXT() with a format string forces the output to a readable date string regardless of cell format. Alternatively, leave the formula as =TODAY() and apply a Date format to the cell directly.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
How to fix it
- 1Select the cell or range showing the serial number. In Excel: Home > Number group > click the format dropdown > choose 'Short Date' or 'Long Date'. In Google Sheets: Format > Number > Date.
- 2To apply a specific format: Excel — Ctrl+1 > Number tab > Date > pick a format. Google Sheets — Format > Number > More Formats > More date and time formats.
- 3If only certain cells in a column show serial numbers, check whether those cells have a Number or General format while adjacent cells have a Date format — apply Date to the affected cells.
- 4After formatting as a date, verify the result looks correct. If the year is 1900, the original number was a small integer, not a real date — investigate the upstream formula.
- 5If a DATEDIF or subtraction formula is displaying as a date (e.g., '00/01/1900') when you expect a number of days, change that cell's format to Number.
Stop hunting errors by hand.
Upload your spreadsheet and the Auditor flags every Serial number and broken formula at once — or paste this one formula and get the fix explained.
Frequently asked
Excel and Google Sheets show different serial numbers for the same date — why?
Excel's epoch is January 1, 1900 (with a historical bug treating 1900 as a leap year, adding 1 to all post-Feb-28-1900 dates). Google Sheets uses December 30, 1899 as day 0. The difference is usually zero for modern dates, but can be 1 for dates in 1900.
My cell shows a date correctly but when I use it in a formula the result is wrong — is the serial number causing this?
No. Formulas use the underlying serial number, which is the same whether the cell is formatted as a date or a number. If the formula result is wrong, the issue is with the formula logic, not the display format.
How do I convert a serial number to a formatted date string in a formula?
Use =TEXT(A2,"YYYY-MM-DD") replacing the format string with your preferred date format. This returns a text string, not a date value, so you cannot do further date math on the result.