Mehrab Imtiaz
Today, in this simple tutorial we will look at how we can convert a date to string in Google Sheets. Beyond direct formatting from the Toolbar, we can go a step further and utilize some of the text functions that Google Sheets has on offer.
How to Convert a Date to a String in Google Sheets
Why Convert Date to a String?
Working with dates in Google Sheets may prove a little trickier than users might realize. While some calculations rely heavily on dates being in the numerical form, in other cases, where we don’t want to change the date format, converting or keeping dates as text may be suitable.
In this guide, we focus on that importance and how we can go about and convert a date into an unchangeable string.
Point to Note: Date vs. Text
In Google Sheets, dates are seen as numerical values. While it is common knowledge, many users don’t fully take advantage of the fact. This is mostly because a date written as a string and a date written as a number don’t look different.
By default, we can differentiate between text and numbers with their alignment, as we can see in this image:
The left-aligned are text values whereas the right-aligned ones are numbers.
But what if the column was uniformly aligned (center-aligned)?
It is impossible to know by sight which value is text or otherwise.
In such a case, we can use the ISTEXT function, and alternatively the ISDATE function, to determine whether the format of the value in the cell is text or not.
Why is format uniformity important?
Cell formatting itself is quite important for either calculation or the security of data. When working with a large amount of data, having a few different forms of cell values can result in an error, and eventually, a lot of time is wasted fixing them.
Quick Convert Date to String in Google Sheets
The quickest way to convert a date, or any format for that matter, to a text or string is to use the apostrophe (‘) character in front of the value.
Another quick alternative to convert a date to a string is to use the TEXTJOIN function.
The formula:
=TEXTJOIN("", TRUE,B3)
While the primary objective of the TEXTJOIN function may not be this, we are simply taking advantage of its capabilities.
We are leaving the delimiter empty and the “ignore_if_empty” field as TRUE. We have only referenced a single cell that contains our date value, cell B3. There are no other cells to join or concatenate. We simply convert to text.
Using the TEXT function to Custom Convert Date to String in Google Sheets
In our previous section, we have discussed a couple of quick solutions to convert a date to a string in Google Sheets. There, we have seen the use of a text function called TEXTJOIN.
This brings us to the best and most customizable way to convert a date to a string, the TEXT function, made solely to convert a value to the text format.
TEXT(number, format)
The application of the function is quite simple: it extracts the value via cell reference and outputs the value in the given text format.
The TEXT function in action:
=TEXT(B3,"mm/dd/yyyy")
Let’s bring our attention to the most important aspect of the function, the “format” field. When it comes to dates, Google Sheets has certain date codes for formatting. This formatting is usually done from the Format tab, but knowing those format codes allows us to utilize them in the TEXT function.
Here’s a list of format codes and their corresponding results that appear when used in the TEXT function:
Day
- D or DD: Returns the day number of the month. DD will always return two digits. E.g., 02 or 15.
- DDD: Returns the shortened day name of the date. E.g., Mon or Tue.
- DDDD: Returns the full name of the day. E.g., Monday or Tuesday
Month
- M or MM: Returns the month number. MM will always return two digits. E.g., 06 or 11.
- MMM: Returns the shortened month name of the date. E.g., Feb or Aug.
- MMMM: Returns the full name of the month. E.g., February or August
Year
- Y or YY: Returns the last two digits of the year. E.g., 05 or 15.
- YYY or YYYY: Returns all four digits of the year. E.g., 2005 or 2015.
Learn more about date formatting in Google Sheets: How to Format Date in Google Sheets (3 Easy Ways)
Extra: Include Time with Date in Google Sheets
In many cases, a worksheet may include timestamps along with dates in the same cell. An example of this can be the result of the NOW function.
While both date and time are numerical values, how they are calculated by google sheets is slightly different.
=TEXT(B3,"HH:MM:SS DD-MMM-YYYY")
Unlike dates, the text code for hours, minutes, and seconds is done in pairs, HH, MM, and SS, since we have a maximum of two digits of each time value.
Notice that “MM” in our formula can be mistaken for the month number. If that code is used on its own on a date-time cell, it will return the month number by default. However, if used alongside other time text codes, you will get minutes instead.
Final Words
That concludes our simple guide to converting a date to a string in Google Sheets. The TEXT function plays a vital role in converting any type of value to its text equivalent, as long as you know the format codes for these values. The function is smart enough to also understand what to extract and present depending on this code.
Feel free to leave any queries or advice you might have for us in the comments section below.
