In this tutorial, you will learn how to format date in Google Sheets.
Different applications and individuals format dates in a number of different ways. For example, here are some eways the date December 25th, 2021 could be formatted:
When working in Google Sheets, it’s important to be consistent about your date formatting to give your spreadsheets a clean professional look and ensure that everyone knows what dates you’re referring to
Understanding Date Formats
Before diving into discussion of date formats, we need to understand how to talk about them. Date formats are typically written as strings of the letters M, D, Y and punctuation. M stands for Month, D for Day and Y for Year, and the number of times the letter repeats specifies the number of digits displayed.
So, for example, the date format YYYY-MM-DD is the full 4-digit year, followed by the 2-digit month and day, all with dashes in between. Eg, 2021-12-25, or 2022-01-20. Note the leading zero in the second example. This is because we specified the 2-digit month with “MM”
Using Standard Date Format
Every Google Sheet has a default date formatting based on the spreadsheet owner’s default country. For example, if you live in the UK your default date format will be DD/MM/YYYY, while if you live in the US it will be MM/DD/YYYY. The spreadsheet location can be modified by opening the Spreadsheet Settings from the File menu, but generally this is a bad idea as it can change other aspects of the spreadsheet as well.
To use the spreadsheet’s default date format, follow these steps:
Select the cell or range you want to format, then click on the Format menu, hover your mouse over the Number submenu to expand it, and choose the Date option
The default date formatting will be applied to the selected cell(s)
Using Custom Date Formats
If you ever want to use date formats other than the default for your region, you can easily do so using the Custom Number Formatting feature in Google Sheets. Here’s how:
Select the cell(s) you want to format
Click on the Format menu, then navigate to the Number submenu, and from there the More Formats submenu at the bottom. Click on “More Date and Time Formats”
In the popup that appears, you can select from a list of preset date and time formats
You can also create your own custom date formats in the field at the top. Add date elements such as day, month or year from the dropdown menu on the right of the field
Use backspace or delete to remove existing elements from the field, and type any desired punctuation using your keyboard
When you’re done, click Apply and the date format will be applied to the selected cell(s)
Example Spreadsheet: Make a copy of the example spreadsheet
In this tutorial, I covered how to format date in Google Sheets. Want more? Check out all the Google Sheets Tutorials.