How To Change the Date Format in Google Sheets

There are many methods to change the date format in Google Sheets; luckily, they’re all pretty straightforward. In this article, I discuss everything there is to know about how to change the date format in Google Sheets.

Specifically, I explain the standard date format. I also show you how to change it to a different locale. Finally, I show a real-world example of how to change the date format in Google Sheets.

Adjusting the Google Sheets Date Format

Did you know Google Sheets has a standard date format? By default, this should be set to your local area. After all, not everyone writes dates the same way around the world.

Your spreadsheet should default to your local format, but that doesn’t mean it’s always right. It also doesn’t mean it’s immutable. You can swap out your preferred date format pretty easily.

Below, I’ll discuss this in practical detail.

How Does Google Sheets Store Dates?

Let’s start by answering the basic questions. Does Google Sheets have a preferred date format? The simple answer is yes. In its internal database, Google Sheets stores all dates as integer numbers (whole numbers that can be positive, negative, or zero).

Here’s what that means.

Instead of the usual sequences of a day, month, and year, Google Sheets prefers simple integers. Here are a few examples. Google Sheets uses:

• 1 for December 31, 1899
• 2 for January 1, 1900
• 102 for April 11, 1900 (note that this is 100 days after January 1, 1900)

So what about dates preceding December 31st, 1899? You may have already guessed it. Google Sheets stores those dates as negative integers. Here are a few examples::

• -1 for December 29, 1899
• -2 for December 28, 1899
• -102 for September 19, 1899

That’s a fun piece of trivia, but does it apply to anything besides dates? You bet.

How Does Google Sheets Store Time?

This is pretty neat. Google Sheets stores time in a very similar way to how it stores dates. Basically, it uses decimals. Here are a few examples of how Google Sheets converts time to decimal points. It uses:

• .00 for 12:00 AM
• .50 for 12:00 PM
• .125 for 3:00 AM
• .573 for 1:45 PM

So let’s put that all together.

How Google Sheets Formats Date and Time

If a user needs to write the date alongside the time, they just append the relevant time decimal to the appropriate date integer. That means, instead of using a date format like April 26th at 1:23 in the morning, Google Sheets can just use the number 31,528.058.

That eliminates the issue of multiple date formats. After all, people in the United States format dates with month, then day. Many others use day, then month. A standard numerical date format eliminates misunderstandings (and that’s absolutely vital for spreadsheets).

Here are a few examples of how Google Sheets formats dates and time as numerals:

• 31,528.058 is April 26, 1986, 1:23 AM
• 43,679.813 is August 2, 2019, 7:30 PM

Date Formats in Google Sheets

Of course, Google Sheets doesn’t just show you what appears to be a random number instead of a date and time. It has multiple built-in date formats. That’s what changes the default value into something readable.

And you’ll want to change this based on your location and spreadsheet preferences. You can choose Month/Day/Year date formats, Day/Month/Year date formats, those with just a month/year, and many more.

If you want a visual on how to change the date format in Google Sheets, I created this video.

So let’s talk about how to modify your date format.

How To Change The Date Format to Another Locale

Before we get into how to change display settings on specific spreadsheets, let’s talk about how to make a universal change to your date format in the settings menu. Google Sheets makes this pretty simple.

This is called changing local. It will help you auto-format dates to your preferred format.

To change the date format in Google Sheets to another locale, users must change the  “Locale” setting to the appropriate date format they want. To do this, follow the instructions below:

1.  Launch Google Sheets and navigate to “File” > “Spreadsheet Settings.”

2. Go to the “Locale” drop-down menu and select a different location. For example, setting the locale to “United Kingdom” will convert your spreadsheet to the “DD/MM/YYYY” format and change the default currency to GBP.
3. Click “Save Settings” to save the change.

Once you have changed your locale, any dates already entered into the Google Sheet will automatically switch to the selected date format of the locale, and any date values entered after this point will conform to the conventions of that location.

This is useful because the European date format follows the DD/MM/YEAR, while the American format follows MM/DD/YEAR. The contrasting formats can cause confusion between American and European users, especially if they are unaware of the differences. However, as you saw, Google Sheets allows you to change the date format easily so that users can customize their options.

How To Change Time Format in Google Sheets

Now that we know how the date is formatted in Google Sheets and how you can change the date format to another locale, it’s time to dive deeper into how to adjust the Google Sheets timestamp and how to work with the default format by using various built-in Google Sheets functions.

Before we get into the default and custom options, let’s look into the Google Sheets QUERY function.

How To Format Dates in Google Sheets Using QUERY

The Google Sheets Query function helps users to format the date, time, and number columns in Google Sheets. Let’s start by looking at the syntax of the Google Sheets Query function.

The basic syntax of the function is:

= QUERY(data, query, [headers])

where

Data – A set of cells you want to request in Google Sheets to perform an inquiry.

Query – A string containing an inquiry using the Google API Query Language. Don’t forget to wrap your query into double quotation marks like this:

=query('data from Airtable'!A:L,"select *")

Or just refer to a cell with the inquiry written in the Google Query language.

Headers – An optional part of the Query formula defines the number of heading rows in your data set.

In the following screenshot, this =QUERY(A1:A8,”SELECT * FORMAT A ‘DD-MMM-YYYY'”) formula was used to format the dates in the cell range A1:A8 from M-DD-YYYY to DD-MMM-YYYY.

Changing the Date Format in the Format Menu

While the default date format is convenient, there are more ways to change the date format in Google Sheets. Let’s discuss another common way to change the way your dates appear in your spreadsheet.

If you want to quickly change multiple cells to your Google Sheets default date format, you can use the steps below:

1. Select the cells you want to format.

2. In the spreadsheet menu, go to “Format” > “Number” > “Date” to see the date time in the chosen format in the cell.

3. The same formats can be found by clicking the “123” icon in the spreadsheet toolbar.

Custom Date Formats

While some users may prefer to use the default Google Sheets format, others prefer to customize their date formats. Luckily, this is simple, convenient, and gives users plenty of creative space.

To customize date formats, follow the steps below:

1. Under the same Google Sheets menu, click on “Format” > “Number” > “Custom date and time.”

2. From here, choose the desired format by clicking “Apply” to save the changes.

This is the easiest way to customize the date and time format using Google Sheets. You can also use the Google Sheets functions to make the process easier.

Convert Date to Value

To convert a date to a value in Google Sheets, you can use the DATEVALUE function. The formula for the DATEVALUE function is:

=DATEVALUE(date)

If you are unsure whether Google Sheets will recognize the format you’re about to enter, you can type the date into another cell first. If the date is identified, it will be centered automatically.

For users who prefer text over numbers, Google Sheets allows you to convert your numbers into text.

Text Function

Various Google functions, such as the TEXT function, make your work faster and more efficient. The TEXT function in Google Sheets automatically converts numbers to text using the following formula:

=TEXT(cell_number,format) 

Number – The function will convert text regardless of the number, date, or time.

Format – The text will be formatted according to the formula you specify.

In the screenshot below, the formula =TEXT(C1,”MM-DD-YYYY”)was used to convert the DATEVALUE in cell C1 into text.

Conclusion

Like every other Google Sheets feature, you only need some learning to work around the dates in Google Sheets. We have discussed how to change the date format in Google Sheets, how to use the date formats you have already been given, such as the default Google Sheets date format, and how to create and customize date formats from scratch.

We also gave you a quick overview of the Query function, which can assist you with this task while converting dates to text and numbers.

This proves that Google Sheets never ceases to amaze us with its built-in functions and features, making our work significantly easier while saving time.

Want to insert a date picker into your spreadsheet? I’ve got you covered. I even made a video with step-by-step instructions on how to do it.

Additional Resources

For further reading, here are some additional resources that can help you with formatting data using Google Sheets:

• Google Sheets Date Picker
• Clear Formatting in Google Sheets
• Insert Timestamps in Google Sheets
• Sort by Date in Google Sheets