Excel gets its default date order from the Windows region, not from an option inside Excel. Change the regional format to English (India) in Windows settings, restart Excel, and new dates will be read day first. Existing dates then need a custom dd/mm/yyyy format.
Where does Excel's date default come from?
There is no setting in Excel itself for the order in which it reads 05/01/2024. It asks the operating system. If your PC is set to a US region, 05/01/2024 means 1 May. If it is set to English (India), it means 5 January. That is why a file can behave differently on a colleague's machine, and why fixing a single file does not fix the next one.
Change the region in Windows
Menu names differ between Windows 10 and 11, and between updates, so read these steps as a guide:
- Open Windows Settings and go to Time and language.
- Choose Language and region. In Windows 10 the same screen is called Region.
- Find Regional format and set it to English (India).
- Open the option to change formats and check the short date. It should read day first, such as dd-MM-yyyy or dd/MM/yyyy. If not, pick a day-first short date from the list.
- Close and restart Excel. A running copy keeps the old setting until it is reopened.
On a Mac, the equivalent is under System Settings, General, Language and Region, where you choose the region. In Google Sheets the setting lives in the spreadsheet: File, Settings, Locale.
What happens to dates I already have?
Nothing, by itself. Changing the region does not convert text, and it does not repair dates that Excel already swapped. It only changes how Excel reads and shows new entries. Existing real dates keep their serial number and will display in the new short format only if their cell format follows the system.
Apply a custom dd/mm/yyyy format to existing dates
- Select the date column.
- Press Ctrl+1 to open Format Cells.
- On the Number tab, choose Custom.
- In the Type box enter dd/mm/yyyy and press OK.
A custom format only changes how the number is displayed. Say the date is the number 45296. It shows as 05/01/2024 with dd/mm/yyyy, as 5-Jan-24 with d-mmm-yy, and as 45296 in General. The value underneath never changes. Use dd-mmm-yyyy if you want the month spelled out, because 05-Jan-2024 cannot be misread by anyone.
In the date list, formats marked with an asterisk follow the system's short and long date settings and will change when the region changes. Formats without one stay put, which is why custom formats are more dependable when files travel.
There is a test that does not depend on typing at all. =DATE(2024,1,5) always builds 5 January 2024, whatever the region. What changes is how it is shown. If the cell displays 01/05/2024, the short date is month first. If it displays 05/01/2024, the short date is day first.
A mismatch between what you type and what appears means the display format and the reading rule are not the same thing. Fix the Windows setting for how Excel reads new entries, and use a custom format for how existing cells look.
Still showing the wrong order?
- Left-aligned dates are text. Formatting does nothing for text, so convert them first with Text to Columns set to DMY.
- A CSV opened by double-click uses the region at that moment. Restart and reopen after the change.
- A date that is both real and wrong, such as 1 May for 5 January, was swapped when it was read, and must be repaired, not reformatted.
A quick test
Type 18/02/2024 in a blank cell. If it turns into a right-aligned date, Excel expects day first. If it stays left-aligned, as text, Excel still expects month first, and the region change did not take. Then type 05/01/2024 and check it shows 5 January.
Useful ideas start with a clearer picture of your own spending.
Explore your reports
