How to change date format in Google Sheets and convert date to number and text

Google Sheets Date Format and Conversion Guide

This article will explore in-depth how dates are stored, formatted, and the conversion between dates and numbers and text in Google Sheets.

How to store dates in Google Sheets

Google Sheets internally stores all dates as integers. It is not the sequence of day, month, and year that we are used to seeing, but simple integers:

• 1 represents December 31, 1899
• 2 represents January 1, 1900
• 102 Represents April 11, 1900 (the 100th day after January 1, 1900)
• And so on.

Unlike Excel that cannot store dates as negative, in Google Sheets, dates before December 31, 1899 will be expressed as negative:

• -1 stands for December 29, 1899
• -2 Representative December 28, 1899
• -102 Represented September 19, 1899
• And so on.

Regardless of how Google Sheets displays the date, it always stores it as an integer. This is the mechanism by which Google Sheets automatically handles dates.

Tip: The same is true for time units, they are just decimals in the table:

• 0.00 means 12:00 am
• 0.50 means 12:00 noon
• 0.125 represents 3:00 am
• 0.573 Representative 1:45 pm
• And so on.

The date and time combination is stored as an integer with a decimal place:

• 31,528.058 Represented at 1:23 am on April 26, 1986
• 43,679.813 Representative August 2, 2019 at 7:30 pm

Change Google Sheets date format to other locales

Another important factor to remember is your spreadsheet locale.

Locale settings preset Google Sheets date format based on your region. So if you are currently in the US, 06-Aug-2019 will appear as 8/6/2019 in your form and 6/8/2019 in the UK.

To ensure that the calculation is correct, especially in files created in other countries, be sure to set the correct locale:

1. In the Google Sheets menu, go to File > Settings.
2. Under the General tab, find Locale settings and select the desired locale from the drop-down list:

Tip: In addition, you can also specify your time zone here to record your file history in it.

Note: Locale does not change the language of Sheets. However, the date format will be applied to the entire spreadsheet. No matter where it is on Earth, everyone using this spreadsheet will see these changes.

How to change the date format in Google Sheets

Don't panic if the date formats in the table are inconsistent, or if all you see is a strange set of numbers. You just need to change the date format in Google Sheets using the built-in tools.

Default Google Sheets date format

1. Select all cells to format.
2. In the spreadsheet menu, go to Format > Numbers and select Date to view only dates, or select Date Time to get both dates and time in the cell:

Integers will be successfully converted into a format that you can recognize at a glance. These are the default Google Sheets date formats:

Tip: If you click the "123" icon on the spreadsheet toolbar, you can also find the same format:

Custom date format

If you don't like Google Sheets' default date format, I won't blame you. Fortunately, you can get creative as you like, thanks to your custom date format.

You can access them from the same Google Sheets menu: Format > Numbers > Custom date and time:

You will see a window with many different custom date formats available. No matter which format you choose and apply, your date looks the same:

If you are still not happy with the date's appearance, you can customize your own custom date format:

1. Select the cell to format.
2. Go to Format > Number > Custom date and time.
3. Place the cursor in the top field containing the date units and use the Backspace or Delete key to delete everything:
4. Click the arrow to the right of the field and select the unit you want to use first. Don't forget to type the separator afterwards. Repeat this until all the necessary units are added (don't worry, you can add or delete them later):

5. Note that there are double arrows to the right of each unit. Click on them and you can adjust the exact way the values ​​are displayed. Here are the options I can choose for "Day":

This way you can edit all values, insert other values ​​and delete outdated values. You are free to separate units with various characters including commas, slashes, and dashs. 6. When you are ready, click "Apply".

Here is the format I created and what my date now looks like:

Format Google Sheets dates using the QUERY function

Another way to change the format of Google Sheets dates is to use formulas. Since this isn't the first time I've shown you QUERY, I'm starting to think of it as a true spreadsheet panacea. :)

I have a sample table where I track shipment of some orders:

I want to change the date format in column B. Here is my QUERY formula:

=QUERY(A1:C7,"select * format B 'd-mmm-yy (ddd)'")

• First, I specify the scope of the entire table – A1:C7
• Then I ask the formula to return all columns – select *
• Also reformat the column B as I put it in the formula – format B 'd-mm-yy (ddd)'

The formula works well. It returns my entire table and changes the date format in column B:

You may have noticed that to change the date format by formula, I used special code that represents the different appearance of day, month and year. If you are not familiar with them, here is a list of these date codes:

Code	illustrate	Example
d	Days without leading zeros (1-9)	7
dd	Days with leading zeros (1-9)	07
ddd	Abbreviation of Day	Wednesday
ddddd	The full name of the day	Wednesday
m (if there are no hours or seconds ahead or behind)	Month without leading zeros	8
mm (if there are no hours or seconds in front or behind)	Month with leading zero	08
mmmm	Abbreviation of the month	August
mmmm	The full name of the month	August
mmmmmm	The first letter of the moon	eight
y or yy	Double-digit years	19
yyy or yyyy	Complete digital year	2019

Tip: If you also want to provide time for date format, you need to add a time unit code. You can find a complete list of time codes in this guide.

With these codes, you can format dates in a number of ways:

• Get only year, month, or date: =QUERY(A1:C7,"select * format B 'yyyy'")

• Returns date, month and day of the week: =QUERY(A1:C7,"select * format B 'dd mmmm, dddd'")

By the way, which date format are you used to using? :)

Google Sheets: Convert dates to numbers

If you need to look at numbers instead of dates, one of the following methods will be useful.

Convert date to number by changing format

1. Select the date cell to convert to a number.
2. Go to Format > Numbers and this time select Numbers among other options.
3. Look! All selected dates have been converted to numbers representing them:

Use the DATEVALUE function to convert dates to numbers

Another way to convert Google Sheets dates to numbers is to use the DATEVALUE function:

=DATEVALUE(date_string) where date_string represents any date in the known format of the spreadsheet. The date should be placed in double quotes.

For example, I want to convert August 10, 2023 to a number. All the following formulas will return the same result: 45148 .

=DATEVALUE("2023年8月10日") =DATEVALUE("2023-8-10") =DATEVALUE("8/10/2023")

Tip: If you are not sure if Google Sheets understands the format you are about to enter, try entering the date into another cell first. If the date is identified, it will be right-aligned.

You can also fill cells with dates in one column and then reference them in formulas in another column:

=DATEVALUE(A2)

Google Sheets: Convert dates to text

Converting dates to text in a spreadsheet is a task that is the TEXT function:

=TEXT(number,format) - number – No matter what number, date, or time you provide to the function, it will return it as text.

• format – The text will be formatted in the format you specify in the formula. Tip: To format correctly, use the same code as the QUERY function.

The actual data formula may look like this:

=TEXT("2023/7/2","YYYY-MM-DD")

Here is how I can convert the date – 2023/7/2 - to text and change the format at the same time:

Google Sheets: Convert text to date

Sometimes, your dates may appear in a way that Google Sheets is completely incomprehensible. This doesn't actually matter, whether they are imported or you just like some kind of view:

Unless you specify a custom format for each cell or change the locale of the spreadsheet, these dates will be formatted as text by default. You won't be able to use them in formulas or other calculations.

But this is the easiest solution for Google Sheets: use the Power Tools add-on to convert text to dates.

It is actually a radio button in other conversion tools.

The tool recognizes all these custom formats in Google Sheets and converts text to dates so that all cells are consistent and can be used for further references:

That's it! I hope so far you have known how to change date format and convert dates to numbers or text in Google Sheets. Feel free to share other cool ways in the comment section below. ;)

The above is the detailed content of How to change date format in Google Sheets and convert date to number and text. For more information, please follow other related articles on the PHP Chinese website!