When you’re working with dates in your Google Sheet, you might not like the default format. You may want to change it to a different format for your convenience. The good thing is that you have several ways to do that in Google Sheets.
3 methods to change the date format in Google Sheets
- Using the default Google Sheet formats
- Using custom date formats
- Using a query function
Method 1: Using the default Google Sheet formats
You can use this method if you’re satisfied with the formats provided by Google Sheets.
Step 1: Highlight the cell you’d like to format.
Tap on the cell you’d like to format. If you want to select multiple cells, click and drag the cursor across the range of cells. Once you’ve highlighted them, you’ll see a blue border. You can also see the cell name or the range of cells in the Name box.
Step 2: Hover over the Number option.
In the main menu bar, tap on Format to see a dropdown menu. Hover over the Number option to see the different formatting options.
Step 3: Choose your desired format.
When you hover over the Number option, you can see the list of options from which you can choose.
To see the day and month in words along with the date and year, you can choose the Friday, September 26, 2008 option.
You can now see that the date has been formatted to the desired format.
Method 2: Using custom date formats
If you’re unhappy with the default format options, you can customize your own using this method.
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. You can also see the range of cells in the Name box.
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. Tap on it.
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.
|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.
You don’t have to struggle to format your dates anymore. We’ve got you covered. You can use any of the above methods to format the dates as you like.