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, we will discuss everything there is to know about how to change the date format in Google Sheets.

Google Sheets Date Format

In its internal database, Google Sheets stores all dates as integer numbers (whole numbers that can be positive, negative, or zero). Instead of the usual sequences of a day, month, and year, it has simple integers, such as:

• 1 for December 31, 1899
• 2 for January 1, 1900
• 102 for April 11, 1900,

Which is 100 days after January 1, 1900

It is worth mentioning that in Google Sheets, dates before December 31, 1899, are displayed as negative numbers, such as:

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

Meanwhile, Google Sheets treats the time units the same, using decimals for your table, such as:

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

If a user needs to write the date alongside the time, they must keep an integer with the appropriate decimal places, such as:

• 31,528.058 is April 26, 1986, 1:23 AM

• 43,679.813 is August 2, 2019, 7:30 PM

How To Change The Date Format to Another Locale

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.

The Default Date Format

While using the Google Sheets functions can be beneficial, using the default date format is also convenient, saving users a significant amount of time.

To use the Google Sheets default date format, follow 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.

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