How to convert numeric dates to text on Google Sheets

Basically, you can adjust the date format on Google Sheets with the style you want such as numbers, letters, . Then the format you set is applied to all the data that we enter. However, in the process of entering date data, we can also convert numeric dates to text to match the content you are working on. The following article will guide you to convert numeric dates to words on Google Sheets.

Convert date numbers to words on Sheets using the format

Step 1:

In the cell or data area that you want to convert from numbers to letters , we highlight and then click Format, select Number .

How to convert numeric dates to text on Google Sheets Picture 1

Step 2:

Then we click on the custom Date and time setting at the bottom of the list.

How to convert numeric dates to text on Google Sheets Picture 2

Step 3:

Now display the custom interface of the time format. Now the user will choose the type of date display as text .

How to convert numeric dates to text on Google Sheets Picture 3

If you do not want to display any fields , then left-click and select Delete in the displayed list.

How to convert numeric dates to text on Google Sheets Picture 4

Finally click Apply to apply the new formatting style to the date.

How to convert numeric dates to text on Google Sheets Picture 5

As a result we will see the text format for the date on Google Sheets as shown below.

How to convert numeric dates to text on Google Sheets Picture 6

Convert numeric dates to text using the Text function Google Sheets

In Google Sheets there is also a format conversion function for you to use. We will use the TEXT function with the formula =TEXT(value, format_text) .

In there:

1. Value: Is the numeric value to be converted to text.
2. Format_text: Is the format to use, providing the format of the format code enclosed in quotes, like "mm/dd/yy".

TEXT supports the following date and time patterns:

1. d for the day of the month as one or two digits.
2. dd for the day of the month as two digits.
3. ddd for the short name of the day of the week.
4. dddd for the full name of the day of the week.
5. m for the month of the year as one or two digits or minutes in the hour. The month will be used unless the code includes hours or seconds as part of the hour.
6. mm for the month of the year as two digits or minutes in the hour. The month will be used unless the code includes hours or seconds as part of the hour.
7. mmm for the short name of the month of the year.
8. mmmm for the full name of the month of the year.
9. mmmmm for the first letter of the month of the year.
10. yy for the year as 2 digits.
11. yyyy gives the year as 4 digits.
12. HH for 24-hour clock time.
13. hh for 12-hour clock time.
14. ss for seconds in an hour.
15. ss.000 for milliseconds in an hour.
16. AM/PM to show the time according to the 12-hour clock and indicate am or pm/evening depending on the time of day.

Note that date/time patterns and # or 0 cannot be combined.

First we have the date table below to apply the formula.

How to convert numeric dates to text on Google Sheets Picture 7

You enter the Text formula in the cell right next to it to convert it to text.

How to convert numeric dates to text on Google Sheets Picture 8

As a result, we get a string of dates in text as shown below.

How to convert numeric dates to text on Google Sheets Picture 9

Convert numeric dates to text on Google Sheets with the QUERY . function

The QUERY function in Google Sheets is used to filter data, and you can use this function to convert numeric dates to text.

The QUERY function formula is =QUERY(data; query; headers) .

In there:

1. data: The range of the data range (eg 'Class A'!A2:E7).
2. query: A query to output the desired data.
3. headers: You can enter a value or leave it blank
4. Usually has 2 values ​​(0 or 1); 0 means no data header column name; 1 means get the title column name).
5. If you leave it blank, the default headers will be 1.

We apply to the date column below with the input formula as shown.

=QUERY(C45:C47;"SELECT C FORMAT C'DD MMMM YYYY'").

We will select all the data of column C with the same selected data with the date format as shown.

How to convert numeric dates to text on Google Sheets Picture 10

When you press Enter, the entire selected data area will be converted to the text date format as shown below.

How to convert numeric dates to text on Google Sheets Picture 11

Kareem Winters

Update 24 July 2023