To change the date format in Google Sheets, select the dates, click Format, Number, then Custom date and time, pick or build the format you want and click Apply. If day and month are the wrong way round everywhere, the fix is the spreadsheet's locale: File, Settings, Locale.
Dates are the most argued-about cells in any shared spreadsheet. One person types a date in numbers meaning the 3rd of April, someone else reads the same digits as the 4th of March, and a booking goes in the wrong month. Google Sheets lets you show dates any way you like, but it helps to understand what's happening underneath before you start changing formats.
Change the date format, step by step
- Select the cells or the whole column that holds your dates.
- Click Format, then Number.
- For a quick change, pick Date or Date time from the list.
- For anything else, click Custom date and time.
- Pick a format from the list, or search the text box at the top for the style you want.
- Click Apply.
In the custom panel you can also build your own format. Google's help page explains that the down arrow on the right of the text box lets you add extra parts, such as the hour or minute, and the small arrows on each part let you choose, for example, whether the month shows as a number, a short name or the full name.
Useful formats to choose from
| You want | Pick a format like | Good for |
|---|---|---|
| Day, month, year | day/month/year | British and most European readers |
| Month, day, year | month/day/year | US readers |
| Full month name | day month-name year | Reports and client documents, no confusion |
| Weekday included | weekday, day month | Rotas and content calendars |
| Date and time | date plus hour:minute | Booking logs and form responses |
When a sheet is shared across countries, I always switch to a format with the month written as a word. Nobody misreads a date that says the month in letters.
The catch: the locale decides how dates are read
Here's the bit that confuses everyone. The format only controls how a date looks. How Sheets reads a date you type in, day first or month first, depends on the spreadsheet's locale. Google says the date and time options you see are based on that locale by default.
If every date you type flips the day and month, change the locale rather than the format:
- Click File, then Settings.
- Under General, open the Locale menu and choose your country.
- Check the Time zone while you're there.
- Click Save settings.
Two things to know before you click save. Google's help page says the change applies to the entire spreadsheet, so everyone working on it sees the new formatting, wherever they're based. And it changes the default number formatting too, not just dates. Changing the locale doesn't change your language settings.
Why some dates won't change format
Underneath, Sheets stores a date as a number. Google's TO_DATE help page explains that it counts the days since 30 December 1899, with any fraction representing the time of day. When a cell holds a real date, it has that number behind it and any date format works.
If you select a column, apply a format and some cells don't budge, those cells are probably text that only looks like a date. That happens a lot with pasted or imported data. Signs to look for: the text sits on the left of the cell instead of the right, or sorting by that column puts dates in an odd order. Retype one of them, or fix the locale and paste the data in again, and it should behave.
Converting a number into a date
Sometimes an export gives you plain numbers where dates should be. Select them and apply Format, Number, Date, and they'll show as dates, because they're already the day counts Sheets understands. The formula version is =TO_DATE(A2). Google says this is equivalent to applying the Date time format from the menu, and if the cell isn't a number, TO_DATE leaves it unchanged.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
Sorting and filtering by date
Once your dates are real dates in a consistent format, sorting by that column puts them in true date order, oldest to newest or the reverse, rather than alphabetical order. That's the quickest test I know: sort the column and see whether anything lands in a strange place. Anything out of order is usually one of those text cells hiding in the list.
Dates from Google Forms and other tools
Form responses and imported files land with a timestamp format that's often more detail than you need. Select the column and pick a shorter date format. The underlying value keeps the time, so you can always switch back if you need it for sorting by time of day.
What I'd do
Set the locale first so typed dates are read the right way, then choose a display format, ideally one with the month as a word for shared sheets. If a few cells won't change, they're text, not dates, so fix the data rather than fighting the format. With your dates fixed, sorting in Google Sheets puts them in true date order, and making a calendar in Google Sheets is a nice way to put them to work.
Frequently asked questions
How do I change the date format in Google Sheets?
Select the cells, click Format, Number, Custom date and time, choose or build a format and click Apply.
Why are day and month the wrong way round in Google Sheets?
The spreadsheet's locale decides how dates are read. Change it in File, Settings, Locale and save.
Does changing the locale affect other people?
Yes. Google says it changes the entire spreadsheet, and everyone working on it sees the change wherever they're based.
Why won't some dates change format?
Those cells are probably text that looks like a date. Real dates are stored as numbers, and only those respond to date formats.
How do I turn a number into a date in Google Sheets?
Apply Format, Number, Date, or use =TO_DATE(A2). Sheets counts dates as days since 30 December 1899.
