Open a workbook, pick a column and say how its dates are written. Dates stored as text become real Excel dates, and every date in the column gets the format you choose. Anything that isn't clearly a date is left alone and listed. See every change first, then download your own file.
Nothing is uploaded. Your file is edited in your browser and never sent anywhere.
Convert Date Format is a free tool to convert date format in Excel files: it turns the dates stored as text in one column into real Excel dates and shows every date in that column in the format you pick, editing your own .xlsx in the browser. The usual file is a bank statement or supplier export from another country, with a Date column that reads 13/05/2024, sorts as text, ignores Ctrl+1 and breaks date formulas.
Only the changed cells in that column differ from your original.
| In the cell now | What the tool does |
|---|---|
| Text date in the order you chose (13/05/2024 as day-month-year) | Becomes a real date in the chosen format |
| Text date starting with a four-digit year (2024-03-05) | Read as year-month-day whatever order you chose |
| Text date typed with an apostrophe ('05/03/2024) | Converted, and the apostrophe marker removed |
| A real date, including a formula whose result is a date | Keeps its value and gets the new format |
| Text that is a date only in another order | Left as text and listed |
| An impossible date (31/02/2024, 29/02/2023) | Left as text and listed |
| A plain number with no date format (45000) | Left alone and listed |
The tool reads a text date as three groups of digits split by the same character twice: a slash, a dash or a dot, so 5/3/2024, 05-03-24 and 2024.03.05 all count. Spaces around the value are ignored, non-breaking ones included. Mixed separators (5/3-2024) are not read.
A converted cell holds the date's serial number with the chosen format, so it sorts in date order and works in EDATE or DATEDIF. Excel recalculates the workbook on opening, so formulas that read the column see dates. We checked the output in Excel for Mac: no repair prompt, and the cells are dates showing what the preview showed.
The 12 formats run from yyyy-mm-dd (the default) through dd/mm/yyyy and d mmm yyyy to dddd, mmmm d, yyyy, with no custom code box. Month and day names are English. The slash and dot are stored escaped, so dd/mm/yyyy shows slashes on every computer rather than the reader's own date separator. Old Mac workbooks on the 1904 date system are counted from 1 January 1904.
04/05/2024 is 4 May in London and April 5 in Chicago, and nothing in the text says which. DATEVALUE, =A2+0 and double-clicking a CSV all read it in the computer's date order, and none of them warns you.
This tool counts how many values fit each order. If one order fits best (one value has a day of 19, say), it fills that order in. If every value reads equally well in more than one order, the page asks you to choose, and Convert stays off until you do.
It never guesses a single cell either. With month-day-year chosen, 13/05/2024 stays text and is listed as "aren't dates in that order, but would be in another".
To find out which order you have, look anywhere in the export for a first or second group over 12. One statement line dated 28/04/2024 settles the whole column.
The tool checks real calendars too. 31/02/2024 and 29/02/2023 stay text, while 29/02/2024 converts because 2024 is a leap year. A formula such as =DATE(2024,2,31) gives no such warning: it rolls over to 2 March.
Two-digit years are read the way Excel reads a typed date: 00 to 29 as 2000 to 2029, 30 to 99 as 1930 to 1999, so a birth date of 15/08/28 lands in 2028. The page flags any and asks you to check the preview.
A CSV written in day-month-year and opened on a computer set to month-day-year (or the other way round) comes in half converted. Every date with a day of 12 or under becomes a real date with day and month swapped: 02/01/2024, meant as 2 January, is now 1 February. Every date with a day of 13 or more stays text, because there is no month 13.
The preview gives this away: text dates to convert and dates to reformat in the same column. Formatting can't repair it, and neither can this tool: it keeps the value of any cell that is already a date, so the swapped ones stay wrong.
Fix the swapped half first:
=IF(ISNUMBER(A2),DATE(YEAR(A2),DAY(A2),MONTH(A2)),A2) // swap only the cells Excel already turned into datesPaste it back over the original as values, save the workbook as .xlsx, then run it through the tool.
Almost always, the cell holds text, and text ignores number formats. Set it to General: a real date shows a number such as 45356 (5 March 2024), a text date doesn't change.
Other causes worth ruling out:
=TEXT(A2,"dd/mm/yyyy"). The tool lists these and leaves them alone. In Excel, drop the TEXT(), point at the date cell and format that.For dates that are already real, you only need a format: select the column, press Ctrl+1, choose Custom and type a code. Type dd\/mm\/yyyy if the slashes must survive another computer's settings.
For text dates, Text to Columns is the fastest built-in route. Select one column, then Data > Text to Columns > Delimited > Next, untick every delimiter, Next, choose Date and the order (DMY, MDY or YMD), and Finish. It gives no list of what it couldn't read, so look for cells still aligned left.
=DATEVALUE(A2) in a helper column, pasted back as values and given a date format with Ctrl+1, works when the text matches your computer's date order. It returns #VALUE! on 24/12/2023 under US settings, and it turns 04/05/2024 into April 5 without a word.
For a CSV that arrives every month, Power Query beats everything else, this tool included, because Excel never gets to guess. Data > From Text/CSV > Transform Data, delete the automatic "Changed Type" step, then right-click the date column > Change Type > Using Locale > Date, English (United Kingdom) or whichever country wrote the file. Next month, refresh.
Once the column holds real dates, the Date Difference Calculator counts days, months, years and working days between them, with the formula for each. If the export's amounts are text too, Convert Text to Number fixes those.
Because the cell holds text that looks like a date, and a number format only changes how numbers look. Set the cell to General: a real date turns into a number like 45356, while a text date stays exactly as it was. Convert the text to a real date first, then any date format works.
Select the column, open Data > Text to Columns, choose Delimited, untick every delimiter, then pick Date and the order the text is written in (DMY, MDY or YMD) and click Finish. DATEVALUE in a helper column also works, but only when the text is written in your computer's own date order.
If the cells are real dates, press Ctrl+1, choose Custom and type mm/dd/yyyy; the value stays the same and only the display changes. If they are text, convert them to dates with the day-month-year order first, or the format will do nothing.
The file was written in one date order and opened on a computer set to the other. Excel read 04/05/2024 in its own order, turning 4 May into April 5, and left dates like 13/05/2024 as text because there is no month 13. Re-import the file with the date order set, or fix the swapped cells with =DATE(YEAR(A2),DAY(A2),MONTH(A2)).
Type =ISNUMBER(A2) next to it: a real date returns TRUE and a text date returns FALSE. Other signs are left alignment in a General cell, an apostrophe in the formula bar, and a status bar that shows Count but no Sum when you select the column.
DATEVALUE reads the text in your computer's date order, so 24/12/2023 fails on US settings because there is no month 24. Worse, 04/05/2024 doesn't fail at all; it quietly becomes April 5. Use Text to Columns with the order chosen, or build the date with DATE, LEFT, MID and RIGHT.