This post is going to show you how to convert dates into the DD/MM/YYYY format in Google Sheets.
The date is an important dimension of every dataset, and you have to ensure the dates are in the proper format.
They are necessary to provide a way to track changes in variables over time.
Dates come in various formats and having your date values formatted properly can be the difference between having a reliable analysis report or not.
Dates have various formats, so the proper format depends on your geographical location or specific requirement.
Why are My Dates In MM/DD/YYYY
There are three main date formats you come across.
- DD/MM/YYYY – 31/12/2022 means 31 December 2022
- MM/DD/YYYY – 12/31/2022 means December 31, 2022
- YYYY/MM/DD – 2022/12/31 means 2022 December 31
Your dates can be in any of these formats for a few reasons. If your date is in the MM/DD/YYYY format, that is because the default location in your spreadsheet is set to the United States.
The MM/DD/YYYY date format is uniquely used in the United States as most other countries use either the DD/MM/YYYY or YYYY/MM/DD date format.
📝 Note: Check out this post if you need to change your dates to the MM/DD/YYYY format instead.
How to Set the Default Date Format to DD/MM/YYYY with Locale Settings
Changing the default date format to DD/MM/YYYY is easy.
- Go the File menu.
- Select the Settings option.
After this step, the Settings for this spreadsheet dialogue box will open.
- Select any non-US country from the list of countries that appear in the dropdown menu in the Locale section.
- Click on Save and reload or Save settings when you’re done.
The date format will be in the style DD/MM/YYYY when the spreadsheet reloads.
Change the Date format to DD/MM/YYYY
There are other ways you can change the date format without altering the default locale setting of your spreadsheet.
You can use the TEXT function, the QUERY function, or the Custom date and time format to change your date format for a single cell.
Change the Date format to DD/MM/YYYY with the TEXT Function
= TEXT ( number, format )
The TEXT function is useful for formatting numbers or dates as text.
= TEXT ( A2, "dd/mm/yyyy" )
The above formula will format the date in the DD/MM/YYYY style. When passing the formatting string, ensure you enclose them in quote marks
⚠️ Warning: Since the TEXT function formats text, the spreadsheet now reads the dates as string values, meaning you cannot use the dates in mathematical operations.
Change the Date format to DD/MM/YYYY with the QUERY Function
= QUERY ( data, query, [headers] )
The QUERY function is a very powerful function that can handle a lot of different tasks, including formatting cell contents.
Check out the complete guide to the QUERY function in Google Sheets to find out more.
= QUERY ( A2:A6, "format A 'dd/mm/yyyy'")
When you copy and paste the above syntax, it will change the date format to DD/MM/YYYY.
The QUERY function is an array function, which means when you enter the formula in the top column, the other cells autofill with the formula result.
💡 Tip: The QUERY function is the preferred alternative to the TEXT function because it retains the date’s numeric format so you can use the results in further calculations.
Change the Date format to DD/MM/YYYY with a Custom Date and Time Format
The Custom date and time feature allows you to pass custom date formatting to a cell. How to do this is simple.
1. Select the cells you want to format
- Go to the Format menu.
- Click on the Numbers option.
- Select the Custom date and time format option.
- Delete the preset formatting instruction using the Backspace key.
- Use the dropdown arrow to select the date components in the order you want them to appear.
Alternatively, you can also choose a custom format from the available preset date formats.
- Click on the Apply button.
💡 Tip: When you’re arranging the date format, remember to add a delimiter between each component to differentiate the day, month, and year components.
Your dates should always be in the appropriate format to prevent errors in your analysis.
The methods described in this post will help you change the date format in your data irrespective of the situation. You can change the default date format in your spreadsheet by changing the Locale setting.
If you don’t want to change the region setting and still want to alter the date format, you can use either the TEXT function, QUERY function, or the Custom date and time format feature.
Have you ever come across the need to alter your date formats in Google Sheets? Do you know any other date formatting tips? Let me know in the comments below!