Change the date format in google sheets

  • 371

You can change the date format in a Google Sheets with the TEXT function and fully change the way the date and time values are displayed in the worksheet.

Dates in Google Wastes are internally stored as figures and the value is equal to the number of days since 30th December 1899, night. The time values are stored as a fractional number.

For case, if the date in a cell is Jan 1, 1990, Google Distance will store the cell value as 2. Still, say Jan 1 19:00 6 PM, the internal date value will be 2 If the date has a time element.75.

The date and time values in Google Wastes are generally displayed in the dd/ mm/ yyyy format, depending on your Spreadsheet locale, but this display format can be fluently customized using the erected-in Textbook function.

For case, a date like15/10/2021 can be displayed as Oct 15, 2021, or in a long format like Friday, October 15, 2021, or you may prize the time element and display it as 0352 PM.

Convert Date Formats in Google Sheets

In Google Sheets, the “TEXT” function allows converting the date and time values in a sheet to a different format. It takes two parameters as shown below.

  1. The preferred format to convert the date or time value to.
  2. The date or time value to be converted.

Sample date formats that you can use in the second parameter of the TEXT function shown below

Date and Time PatternResult
MMMM d, YYYYOctober 21, 2021
MMM d, YYYYDec 3, 2021
dd, MMMM DD YYYYYTue, October 19, 2021
dd MMM YYYY06 Feb 2022
dd/mm/yy h:mm22/10/21 22:31
hh:mm:ss am/pm01:11:39 PM
h:mm am/pm9:58 PM
MMM-dd h:mm am/pmOct-20 10:37 PM
MMM DD, ‘YY h:mm am/pmOct 31, ‘21 10:34 AM

To view the complete list in Google Sheet, Click Here.

Repeated Pattern in Custom Date Formats

The placeholders (like d, m, or y) have different meanings depending on the number of pattern letters.

For case, if the input date is October 5, the format law d will display the day of the month as 5 but if the format law is dd it’ll display zero-padded value as 05. Still, the result is an abbreviated day of the week Tue but if the format law is dddd, the full day of the week as Tuesday gets displayed, If the format law is ddd.

Also, for the month placeholder, mm will display the zero-padded numerical value but mmm and mmmm will display the abbreviated and full month name independently.

Date Formats with Array Formulas

Still, you can use an array formula in a new column to convert the dates, If you have a date column in Google Wastes and you want to display the date in a different format.

Assuming that the date column is in cell A1, you can use the following array formula in the first cell of an empty column to display the same date and time value but in a different format.

  IF(ROW(A:A)=1,"New Date Format",
  IF(ISBLANK(A:A),"",TEXT(A:A, "MMMM dd, YYYY"))))

This can be veritably handy for Google Wastes that are storing Google Form responses. Google Distance will always show the response timestamp in your locale but you can add a new column to display the date and time in a different format.


You can change the date format in a Google Sheets with the TEXT function and fully change the way the…

You can change the date format in a Google Sheets with the TEXT function and fully change the way the…

Leave a Reply

Your email address will not be published.