3 methods to change the date format in Google Sheets
- Apply custom date formats
- Using a query function
Method 1: Apply custom date formats
Step 1: Highlight the cells you’d like to format.
Select the range of cells you’d like to format. Once you’ve selected them, you can see the cells surrounded by a blue border.
Step 2: Go to the Custom date and time option.
Tap on Format in the main menu bar. Hover over the Number option.
From the list of options, you can find the Custom date and time option at the bottom.
Step 3: Customize the date format as you wish.
After clicking the Custom date and time option, you’ll see a dialog box. You can see multiple formatting options that you can choose from.
You can pick one of the formats by clicking on it and then clicking the Apply button.
For instance, let’s choose the sixth format here—the one with the slashes.
You can now see that the format has been applied to your date.
Alternatively, you can look at all the possible options by clicking the dropdown icon. It’s present before the Apply button.
You can add any of the details from the options given. Just tap on the option to add it.
To delete a detail already present, click on the icon next to the option.
Select the Delete option to delete it.
After all your modifications, click on the Apply button to apply all your changes. You can see that the date has disappeared now.
Method 3: Using a query function
Step 1: Select a cell to display the formatted date.
Note down the cell with the date you’d like to format. It’s A1 here. Now, select a cell you’d like to display the date in.
You’ll be typing in the query function here.
Step 2: Type the query function in the chosen cell.
To format the date to 12-December-22 (Monday), we use the following query function:
=QUERY(A1,"select * format A 'd-mmmm-yy (dddd)'")You can customize the formula as you like. Use the table below to understand the codes for different formats.
| Code | Description | Example |
| d | Day without a leading zero for 1-9 | 9 |
| dd | Day with a leading zero for 1-9 | 09 |
| ddd | Day as an abbreviation | Mon |
| dddd | Day as a full name | Monday |
| m | Month without a leading zero | 8 |
| mm | Month with a leading zero | 08 |
| mmm | Month as an abbreviation | Dec |
| mmmm | Month as a full name | December |
| mmmmm | First letter of the month | D |
| y or yy | Two digit year | 19 |
| yyy or yyyy | Full numeric year | 2019 |
Enter the query function in the chosen cell.
Step 3: Press Enter.
After you’ve entered the query function, press Enter to see the format applied to your cell.
Wrapping up
We hope you found this article useful.