How To Format Date In Google Sheets

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:

  • 12/25/2021
  • 25/12/21
  • 2021-12-25
  • 25-DEC-2021

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:

Step 1

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

Step 2

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:

Step 1

Select the cell(s) you want to format

Step 2

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”

Step 3

In the popup that appears, you can select from a list of preset date and time formats

Step 4

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

Step 5

Use backspace or delete to remove existing elements from the field, and type any desired punctuation using your keyboard

Step 6

When you’re done, click Apply and the date format will be applied to the selected cell(s)

Summary

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.