You type or import a column of dates, and Excel shows 45366, 45367 and 45368 instead. The good news: your data is almost always fine. Excel is simply showing you the raw number it uses for every date, because the cell is not formatted to display it as one.
This guide explains what those numbers mean, how to turn them back into readable dates in a few clicks, and how to deal with the trickier opposite case, where dates look right but Excel treats them as plain text. The steps are for Microsoft 365 and Excel 2016 or later on Windows.
What the number actually means
Excel stores every date as a serial number: a count of days. In the default 1900 date system, 1 is 1 January 1900, 2 is 2 January 1900, and so on. 45366 is 15 March 2024, because that is 45,366 days into the count.
Times are stored as the decimal part of the same number. 0.5 is noon, 0.25 is 6:00 am, and 45366.75 is 15 March 2024 at 6:00 pm.
The number is what Excel calculates with. The number format decides how it looks. Formatted as a date, 45366 shows as 15/03/2024 or 15-Mar-2024. Formatted as General or Number, you see the serial itself. That is the whole problem in most cases: the value is right, the format is wrong.
This design is also why date maths works. =B2-A2 gives the days between two dates, and =A2+30 gives the date a month or so later, because both are just numbers underneath.
Why your dates turned into numbers
A few situations cause almost every case:
- The column was formatted as General or Number. If someone applied a Number format to the whole column, or the sheet was built from a template that did, every date in it shows as a serial.
- Values were pasted from somewhere else. Paste Values, or copying from another workbook, keeps the number but can drop the date format.
- A formula inherited the wrong format.
=A2+30or=MAX(A2:A50)placed in a General cell often shows a serial, even though the result is a correct date. - A CSV or export wrote serials. Some accounting tools, databases and scripts write dates as raw numbers. A CSV holds no formatting at all, so Excel has nothing to tell it those numbers are dates.
- Power Query or a pivot table output. A query step that changes a column to a whole number or decimal type, or a value field in a pivot table summarising dates, produces serials.
The 1900 and 1904 date systems
Old workbooks made in Excel for Mac may use the 1904 date system, where day 0 is 1 January 1904. The same date is stored as a number 1,462 lower than in the 1900 system, which is roughly four years and one day.
If dates copied between two workbooks suddenly jump by about four years, this is the reason. Check the setting under File › Options › Advanced, in the section When calculating this workbook, at Use 1904 date system. Changing it shifts every existing date in that workbook, so it is usually safer to correct the copied values by adding or subtracting 1462.
The date that never existed
Serial 60 is 29 February 1900, a day that never happened, since 1900 was not a leap year. Excel kept it on purpose for compatibility with Lotus 1-2-3. It only affects dates before 1 March 1900, so for modern data you can ignore it, but it explains why very old dates can be off by one.
Quick diagnosis checklist
Before fixing anything, find out which problem you have. Click a cell in the date column and check:
- What does the formula bar show? A number such as 45366 means the value is a real date that only needs a format. Text such as 15/03/2024 needs another check.
- Which way is it aligned? With no alignment set, Excel puts numbers and real dates on the right and text on the left. Left-aligned dates are usually text.
- What does ISNUMBER say? In an empty column, enter
=ISNUMBER(A2)and fill down. TRUE means a real date (or serial); FALSE means text. - Does sorting work? If Sort Oldest to Newest puts 02/01/2025 before 15/03/2024, the column is being sorted as text.
- Is there a green triangle? A small green mark in the corner of a cell often means a number or date stored as text.
If the answer is “a real number with the wrong format”, use the first fix below. If it is “text”, skip to the section on text dates.
Fix 1: format the cells as dates
This changes only how the numbers look. Nothing in the data changes, so it is completely safe.
- Select the cells or the whole column.
- Press Ctrl+1 to open Format Cells.
- On the Number tab, choose Date and pick a style from the list.
- For an exact layout, choose Custom instead and type a code such as
dd/mm/yyyy,yyyy-mm-ddordd-mmm-yyyy. - Click OK.
There is also a faster route: Home › the Number Format box in the Number group › Short Date or Long Date.
Keep in mind that Short Date follows your Windows regional setting, under Settings › Time & language › Language & region. On a PC set to India or the UK it shows 15/03/2024; on one set to the US it shows 3/15/2024. If the file will be opened on other computers and the layout matters, use a Custom format so it looks the same everywhere.
If you see ######## after formatting, the column is just too narrow. Double-click the right edge of the column header to widen it.
Fix 2: turn the date into text with TEXT
Sometimes you need the date as characters, not as a date value, for example to build a label like “Invoice due 15/03/2024” or to paste into a system that only accepts text. Use:
=TEXT(A2,"dd/mm/yyyy")
The result is text. It looks right, but it will not sort, filter or calculate as a date, and it is fixed in the layout you typed. Use it for display and exports, and keep the original date column for everything else.
The opposite problem: dates stored as text
Here the cells look like dates, but Excel sees words. Signs: they are left-aligned, =ISNUMBER(A2) returns FALSE, sorting puts them in alphabetical rather than date order, and formulas such as =A2+30 return #VALUE!.
Text to Columns
This built-in tool converts a column of text dates in one pass and lets you say which order they are written in.
- Select one column of dates.
- Go to Data › Text to Columns, choose Delimited and click Next.
- Clear every delimiter box and click Next.
- Under Column data format, choose Date and pick the order of the text: DMY, MDY or YMD.
- Click Finish, then apply a date format with Ctrl+1.
DATEVALUE
=DATEVALUE(A2) reads text and returns a serial, which you then format as a date. The catch is that it reads the text using your computer’s regional setting, so 03/04/2024 becomes 3 April on one PC and 4 March on another.
A formula for fixed dd/mm/yyyy text
When every value is written exactly as two-digit day, two-digit month and four-digit year, this formula gives the same answer on any computer:
=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))
Format the result as a date, copy it, and use Paste Special › Values over the original column. If you also have numbers stored as text in other columns, the same pattern applies, and the convert text to numbers tool handles them in bulk.
When half the dates are swapped
This is the most dangerous version, because it hides. Suppose a CSV from a US system writes month/day, and you open it on a PC set to India or the UK, which expects day/month.
- 03/04/2024 (meant as March 4) is read as 3 April. It becomes a real date, right-aligned, and looks perfectly normal, but it is wrong.
- 03/15/2024 cannot be the 3rd of month 15, so Excel leaves it as text, left-aligned.
The result is a column where some cells are real dates and others are text, and the real ones are the wrong ones. To spot it, run =ISNUMBER(A2) down the column: a mix of TRUE and FALSE is the giveaway. Another hint is that every value marked TRUE has a day of 12 or less.
The cleanest fix is to stop Excel converting the file on open. Import it through Data › From Text/CSV and set the locale to match the source, or convert it first with the CSV to Excel converter, which builds the workbook without Excel’s automatic date guessing so you can fix dates deliberately.
Fix dates online without formulas
If the column mixes several problems at once, serials, text dates, swapped day and month and different styles, the free Fix dates tool sorts them out in one step. It runs entirely in your browser, so the file never leaves your device.
- Finds the date columns for you. Columns that mostly hold dates, or have a header like Date or Due, are picked automatically. You can also choose the columns yourself.
- Reads many styles. ISO dates such as 2024-03-15, dd/mm/yyyy and mm/dd/yyyy with slashes, dashes or dots, two-digit years, English month names such as 15 Mar 2024 or March 15, 2024, compact 20240315, and times.
- Converts serial numbers, including serials stored as text in a CSV.
- Lets you decide ambiguous dates. A value like 03/04/2024 follows your setting, day/month by default. Dates such as 15/03/2024 can only mean one thing and are always read correctly.
- Writes the format you want. Choose DD/MM/YYYY, MM/DD/YYYY, YYYY-MM-DD or DD-MMM-YYYY, saved as real Excel dates or as text. A CSV has no date type, so CSV output is always text.
- Never guesses. Impossible dates such as 31/02/2024 and words such as TBD stay unchanged and are listed with their cell addresses so you can check them.
You do not even need to know which problem you have. The file check that runs when you open a file on any SheetTidy page flags dates stored as numbers in columns with date-like names, so you know where to look before you start.
Troubleshooting
The format changed but the cells still show numbers. The values are probably text that looks like numbers. Check with =ISNUMBER(A2); if it says FALSE, convert them first, then format.
Formatting as a date gives a year like 1900 or 1905. The numbers are not date serials. They might be codes, amounts or compact dates like 20240315, which Excel reads as a serial far in the future and shows as ########. Compact dates need converting, not formatting.
Dates are about four years out. The workbook uses the other date system. See the 1904 section above.
Everything looks fine, but dates are in the wrong month. Day and month were swapped on import. Re-import with the correct locale, or reconvert the original text with the right order.
I need the dates to stay as text for an upload. Use =TEXT() or choose text output in the Fix dates tool. If you will later save the sheet as a CSV with the Excel to CSV converter, pick the date layout the receiving system expects first.
The dates turn back into numbers every time I reopen the CSV. A CSV cannot store formatting. Save your work as .xlsx to keep the date format.