Have you ever wondered how to change the date format in Google Sheets? Look no further! In this article, I will share with you everything you need to know about modifying the date format in Google Sheets.
Changing the Date Format in Google Sheets
Did you know that Google Sheets has a default date format? By default, it should be set to your local region. After all, not everyone writes dates the same way around the world.
Your spreadsheet should be configured to your local format by default, but that doesn’t mean it’s always correct. And it also doesn’t mean it’s set in stone. You can easily change the date format to your preference.
Here’s how to do it.
How Does Google Sheets Store Dates?
Let’s start by answering some basic questions. Does Google Sheets have a preferred date format? The answer is yes. In its internal database, Google Sheets stores all dates as integer numbers (integer numbers can be positive, negative, or zero).
Here’s what that means.
Instead of the usual sequence of day, month, and year, Google Sheets prefers simple integer numbers. Here are some examples. Google Sheets uses:
- 1 for December 31, 1899
- 2 for January 1, 1900
- 102 for April 11, 1900 (note that this represents 100 days after January 1, 1900)
And what about dates earlier than December 31, 1899? You may have already guessed it. Google Sheets stores those dates as negative integer numbers. Here are some examples:
- -1 for December 29, 1899
- -2 for December 28, 1899
- -102 for September 19, 1899
This is fun information, but does it apply to anything other than dates? Absolutely.
How Does Google Sheets Store Time?
It’s quite interesting. Google Sheets stores time in a very similar way to how it stores dates. Essentially, it uses decimal numbers. Here are some examples of how Google Sheets converts time into decimal points:
- .00 for midnight
- .50 for noon
- .125 for 3:00 AM
- .573 for 1:45 PM
Now let’s put it all together.
How Does Google Sheets Format Date and Time?
If a user needs to write the date with the time, they simply add the corresponding decimal of the time to the appropriate whole date. This means that Google Sheets can simply use the number 31,528.058 instead of using a date format like “April 26 at 1:23 AM”.
This solves the problem of multiple date formats. After all, Americans format dates with the month first, then the day. Many other countries use the day first, then the month. A standard numeric date format eliminates misunderstandings (and it’s absolutely essential for spreadsheets).
Here are some examples of how Google Sheets formats dates and time as numbers:
- 31,528.058 corresponds to April 26, 1986, at 1:23 AM
- 43,679.813 corresponds to August 2, 2019, at 7:30 PM
Date Formats in Google Sheets
Of course, Google Sheets doesn’t just show you what seems to be a random number instead of a date and time. It offers several predefined date formats. This is what allows you to change the default value to something readable.
And you will want to change this according to your location and spreadsheet preferences. You can choose month/day/year formats, day/month/year formats, ones with just a month/year, and many more.
If you want to visually see how to change the date format in Google Sheets, I’ve created this video for you.
So, let’s talk about how to change your date format.
How to Change the Date Format for Another Region
Before I explain how to change the display settings on specific spreadsheets, let’s talk about how to universally change the date format in the settings menu. Google Sheets makes this quite simple.
It’s called changing the locale. This will help you automatically format dates according to your preferred format.
To change the date format in Google Sheets for another region, users need to modify the “Locale” setting to select the appropriate date format. Follow the instructions below:
- Open Google Sheets and go to “File” > “Spreadsheet settings”.
- In the “Locale” dropdown menu, select a different location. For example, by setting the locale to “United Kingdom”, your spreadsheet will convert to the “DD/MM/YYYY” format and the default currency will be GBP.
- Click “Save settings” to save the change.
Once you have changed your locale, any dates already entered in the Google Sheets spreadsheet will automatically switch to the selected date format for that locale, and any dates entered after that point will conform to the conventions of that location.
This is useful as the European date format follows the DD/MM/YYYY format, while the American format follows the MM/DD/YYYY format. Contrasting formats can cause confusion between American and European users, especially if they are not aware of the differences. However, as you have seen, Google Sheets allows you to easily change the date format so that users can customize their options.
How to Change the Time Format in Google Sheets
Now that we know how dates are formatted in Google Sheets and how you can change the date format for another region, let’s dive deeper into adjusting the date and time in Google Sheets using various built-in Google Sheets functions.
Before we discuss default and custom options, let’s look at the QUERY function in Google Sheets.
How to Format Dates in Google Sheets Using QUERY
The QUERY function in Google Sheets allows users to format 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 as follows:
=QUERY(data, query, [headers])
Where:
Data: A range of cells you want to query Google Sheets to perform a search.Query: A string containing a query using the Google API Query Language. Remember to enclose your query in double quotation marks like this:=query('data from Airtable'!A:L,"select *").Headers(optional): An optional part of the Query formula that sets the number of header rows in your data range.
In the screenshot below, the formula =QUERY(A1:A8,"SELECT * FORMAT A 'DD-MMM-YYYY'") was used to format the dates in the range of cells A1:A8 from the M-DD-YYYY format to the DD-MMM-YYYY format.
Changing the Date Format in the Format Menu
While the default date format is handy, there are other ways to change the date format in Google Sheets. Let’s discuss another common method of changing how your dates appear in your spreadsheet.
If you want to quickly change the format of multiple cells to the default date format in Google Sheets, you can follow the steps below:
- Select the cells you want to format.
- In the spreadsheet menu, go to “Format” > “Number” > “Date” to see the date and time format you chose in the cell.
- The same formats can be found by clicking on the “123” icon in the spreadsheet toolbar.
Custom Date Formats
While some users prefer to use the default Google Sheets format, others prefer to customize their date formats. Fortunately, this is simple, convenient, and gives users a lot of creative freedom.
To customize date formats, follow the steps below:
- Under the same Google Sheets menu, click on “Format” > “Number” > “More Formats” > “More date and time formats”.
- Choose the desired format and click “Apply” to save the changes.
This is the easiest way to customize the date and time format using Google Sheets. You can also use Google Sheets functions to make the process even easier.
Converting 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’re unsure if Google Sheets recognizes the format you’re going to enter, you can first type the date in another cell. If the date is recognized, it will automatically be centered.
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(number_cell,format)
Where:
Number: The function will convert the text regardless of whether it’s a number, date, or time.Format: The text will be formatted based on the formula you specify.
In the screenshot below, the formula =TEXT(C1,"MM-DD-YYYY") was used to convert the DATEVALUE of cell C1 into text.
Conclusion
Like all other features in Google Sheets, all you need is to learn a few tricks to work with dates in Google Sheets. We have explained how to change the date format in Google Sheets, how to use the date formats you already have, such as the default Google Sheets date format, and how to create and customize date formats from scratch.
We have also given you a quick overview of the QUERY function, which can assist you in 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, which greatly simplify our work while allowing us to save time.
Do you want to insert a date picker in your spreadsheet? I’ve got you covered. I even made a video with step-by-step instructions on how to do it.
Additional Resources
To further enhance your date formatting skills using Google Sheets, here are some additional resources that can help you:
