Google Sheets always has one default date format applied to the spreadsheets and that varies from user to user, depending on the geographical location selection of the user’s system.
The best thing is that Google Sheets has the flexibility to change the default date format and choose and apply any date format from the other available date formats or customize as well based on the user’s requirement.
In this tutorial, we will show you how to update the default date format, change the date format and customize the date format in Google Sheets.
Steps to Change the Default Date Format as per Location
The default date format depends on the selected location of your Google Sheets. For example, if your spreadsheet location is selected to the US, the default date format would be “MM/DD/YYYY” and if you have chosen the UK the date format would be “DD/MM/YYYY”.
- First, go to the “File” tab and then click on the “Settings” option.
- After that click on the “Locale” field downward arrow and choose your locale/location to update the default date format as per that location selection.
- Once done, click on “Save and Reload”.
Change the Date Format to your Default Date Format
- First, select the cell range that has the dates as the value in them and go to the “Format” tab.
- After that, click on the “number” option and then click on the “Date” option to apply the default date format of your Google Sheets.
- At this moment, the date values got formatted as per your default date format.
Steps to Change Date Format in Google Sheets
- First, select the cell range that has dates as the value in them, and then go to the “Format” tab and click on the “number” option and then click on the “Custom date and time” option.
- Once you click on the “Custom date and time” option, you will get the “Custom date and time formats” dialog box opened to choose any date format out of the available ones.
- Now, choose and click any date format from the available ones, per your requirement, and then click on the “Apply” button.
- The moment, you click on the “Apply” button, the new date format will get applied to the selected cells.
Create and Apply Your Custom Date Format
Sometimes you may not find the date format that you want to apply from the available date formats, in that situation Google Sheets, provide you the option to create your custom date format to apply.
- First, select the cells with the dates as values, and open the “Custom date and time formats” dialog box as mentioned in the above steps.
- Once opened, click on any date format to highlight the date format field names above.
- After that, go to the date field names and click on them one by one and select the “Delete” option to delete them and the backspace button to remove the separators.
- Now, click on the downward arrow and choose the fields (Day, Month, or Year) of the date format one by one in the sequence you want your date values.
- After selecting the custom date format field, you can click on that field to further customize that field if you want.
- Now, you can choose how you want to display that field in the date values.
- Once done, add the rest of the date format fields to complete the data format as you want. Do not miss to add separators between them.
- After that, click on the “Apply” button and your customized date format will get applied to the selected cells.
- At this point, your date values are got converted based on the above-customized date format.
Note – Sometimes you may find difficulty in adding the separators after every field, so to get rid of this, after adding the second field, remove that field and you will become able to add the separator after the first field and then after adding the separator add the second and third fields. After that remove the third field and add the separator after the second field. Continue the same until you are done with the date format.
Change Date Format Using Query Formula
You can also change the date format with the help of the “QUERY” formula as shown below:
=QUERY(A2:A7,”format A ‘ddd-d-mmm-yy'”)
- In the first step, we have given the cell reference (A2:A7) that has the value as dates.
- In the second step, we have given the command to format the column A values in (ddd-d-mm-yy) date format.
You can use the following table to format dates using the “QUERY” formula
Other Ways to Change Date Format
- =DATEVALUE – With this formula, you can convert the date values into numbers. As you all know spreadsheet recognizes and saves the dates as numbers in the backend.
- =TEXT – With this formula, you can convert the date into text and also change the date format.