The TEXT function in Google Sheets allows you to totally modify the way the date and time data are displayed in the worksheet by customising the date format.
Internally, Google Sheets stores date as numbers, with the value equal to the number of days since December 30, 1899, at midnight. A fractional integer is used to hold the time values.
For example, if a cell contains the date January 1, 1990, Google Sheet will retain the cell value as 2. The internal date value will be 2.75 if the date contains a time component, such as January 1 1900 6 PM.
Depending on your Spreadsheet location, date and time data in Google Sheets are generally presented in the dd/mm/yyyyy format, however this display format may be readily modified using the built-in TEXT function.
For example, a date of 15/10/2021 may be presented as Oct 15 2021, Friday, October 15 2021, or you can extract the time component and display it as 03:52 PM.
Convert Date Formats in Google Sheets
The TEXT function of Google Sheets allows to convert the date and time values in a sheet to a different format. It takes two parameters:
- The date or time value to be converted.
- The preferred format to convert the date or time value to.
=TEXT(A1, "MMMM d, YYYY")Here are some sample date formats that you can use in the second parameter of the TEXT function:
| Date and Time Pattern | Result |
|---|---|
| MMMM d, YYYY | October 21, 2021 |
| dd-MMM-YYYY | 08-Dec-2021 |
| MMM d, YYYY | Dec 3, 2021 |
| dd, MMMM DD YYYYY | Tue, October 19 2021 |
| ddd | Tuesday |
| d/MM/YYYY | 30/11/2021 |
| dd MMM YYYY | 06 Feb 2022 |
| mmm-yy | Oct-21 |
| dd/mm/yy h:mm | 22/10/21 22:31 |
| hh:mm:ss am/pm | 01:11:39 PM |
| h:mm | 14:23 |
| h:mm am/pm | 9:58 PM |
| MMM-dd h:mm am/pm | Oct-20 10:37 PM |
| MMM DD, ‘YY h:mm am/pm | Oct 31, ‘21 10:34 AM |
You can view the complete list in this Google Sheet.
Repeated Pattern in Custom Date Formats
Depending on the amount of pattern letters, the placeholders (such as d, m, or y) have varied meanings.
If the input date is October 5, the format code d will display the day of the month as 5, but if the format code is dd, the zero-padded value will be displayed as 05. The result is an abbreviated day of the week, Tue, if the format code is dddd, while the full day of the week, Tuesday, is displayed if the format code is dddd.
Similarly, mm displays the zero-padded numerical value for the month placeholder, whereas mmm and mmmm display the abbreviated and full month names, respectively.
Date Formats with Array Formulas
If you have a date column in Google Sheets and wish to show it in a different format, you may convert the dates using an array formula in a new column.
Assuming the date column is in cell A1, you can show the same date and time data in a different manner by using the following array formula in the first cell of an empty column.
=ARRAYFORMULA(
IF(ROW(A:A)=1,"New Date Format",
IF(ISBLANK(A:A),"",TEXT(A:A, "MMMM dd, YYYY"))))This is particularly useful for Google Sheets that store Google Form replies. The response timestamp in your location will always be displayed in Google Sheet, but you may add a new column to display the date and time in a different format.
Also Read: Top 6 Ways to Fix JPG Files That Won’t Open on Windows 10 and Windows 11
