There are various ways to change the date format in Google Sheets. Luckily, they’re all pretty simple.
This article will tell you everything you need to know about how to change the date format in Google Sheets.
Contents
Google Sheets Date Format
Google Sheets stores all dates in its internal database as integers (integers that can be positive, negative, or zero). Instead of the usual day, month, year sequence, it contains simple integers like this:
- 1 December 31, 1899
- 2 January 1, 1900
- 102 for April 11, 1900,
This is 100 days after January 1, 1900
Note that in Google Sheets, dates before December 31, 1899 are displayed as negative numbers, like this:
- -1 for December 29, 1899
- -2 for December 28, 1899
- -102 for September 19, 1899
Google Sheets, on the other hand, treats time units the same, using decimals in tables like this:
- .00 for 12:00 AM
- .50 for 12pm
- .125 3AM
- .573 1:45 PM
If the user needs to write the date along with the time, you should keep it as an integer with proper decimal places, like this:
- 31,528.058 is April 26, 1986 at 1:23 AM
- 43,679.813 is on August 2, 2019 at 7:30 PM
How To Change The Date Format to Another Locale
To change the date format in Google Sheets to a different locale, the user must change the “Locale” setting to the appropriate date format desired.
To do this, follow these steps:
- Launch Google Sheets and go to File > Spreadsheet Settings.”
- Go to the Locale dropdown menu and select a different location. For example, setting the locale to “United Kingdom” converts the spreadsheet to “DD/MM/YYYY” format and changes the default currency to his GBP.
- Click Save Settings to save your changes.
When you change locales, dates already entered in Google Sheets will automatically switch to the date format you chose for that locale, and date values entered thereafter will follow the rules of that location.
This is useful because the European date format follows DD/MM/YEAR whereas the American date format follows MM/DD/YEAR. Contrasting formats can cause confusion between American and European users, especially if they are unaware of the difference. However, as you can see, Google Sheets makes it easy to change the date format, allowing users to customize their options.
How To Change Time Format in Google Sheets
Now that you know how dates are formatted in Google Sheets and how to change the date format to a different locale, let’s look at how to adjust timestamps in Google Sheets and use various built-in features and how to manipulate the default format. Functions in Google Sheets.
Before we get into the default and custom options, let’s take a look at the QUERY function in Google Sheets.
How To Format Dates in Google Sheets Using QUERY
The Google Sheets Query feature helps users format date, time, and number columns in Google Sheets. First, let’s take a look at the syntax of query functions in Google Sheets.
The basic syntax for functions is:
= QUERY(data, query, [header])
where
Data – A set of cells to request in Google Sheets to perform a query.
Query – A string containing a query using the Google API Query Language. Don’t forget to enclose the query in double quotes like this:
=query(‘Data from Airtable’!A:L,”select *”)
Or simply reference a cell containing a query written in the Google Query Language.
Header – An optional part of the query expression that defines the number of header rows in the data set.
In the following screenshot, we use this =QUERY(A1:A8,”SELECT * FORMAT A ‘DD-MMM-YYYY’”) formula to extract dates in cell range A1:A8 from M-DD-YYYY to DD- Formatting up to . Hmm, YYYY.
The Default Date Format
Using the Google Sheets functions is beneficial, but using the default date format is also convenient and saves users a lot of time.
To use the default date format for Google Sheets, follow these steps:
