How to change date format in Google Sheets

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.

dDay without a leading zero for 1-99
ddDay with a leading zero for 1-909
dddDay as an abbreviationMon
ddddDay as a full nameMonday
mMonth without a leading zero8
mmMonth with a leading zero08
mmmMonth as an abbreviationDec
mmmmMonth as a full nameDecember
mmmmmFirst letter of the monthD
y or yyTwo digit year19
yyy or yyyyFull numeric year2019

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.

Leave a Comment