Today I was just about to write a Google Sheet Query function but I soon realised I should first write few other Google sheet tutorials related to it. Why? Because it’s necessary to explain the Query function in certain cases. I mean when date appears in our data and we may want to use it as as a Query filter criteria. So I decided to write this tutorial related to Google Sheet date and text function first. Learn convert date to text or string in Google Sheets. What we are going to learn here is not the simple date to string text function, i mean the compact form. Of course I will explain that also. We should know how to convert date to string using the long-winded approach. So that we can perfectly convert date to string.
Should I Limited to Convert Date to String Using the Long-winded Approach Only?
No! You can convert date using the compacted approach also. But when you are working on a shared Google Sheet, you must know both form. Other wise you can’t understand the Query formula used in certain situations.
What are the Requisites to Convert Date to String Using the Long-winded Approach in Google Sheets?
To convert date to string using long-winded approach, you should first know three date functions and a text function. I will explain all the functions one by one. The below are the three simple date functions in use and the text function follows.
Year Function in Google Sheets
Use this function to get the year from a given date. For example the given date is 20/08/2017. When you use this function you will get the result 2017. You can either use;
=year(“20/08/2017”) or use =year(A6) where A6 is a cell reference
Day Function in Google Sheets
You can use the above day function to get the day of the month from a given date. Suppose the given date is 20/08/2017, when this function in use you will get the result 20. You can either use the function as;
=day(“20/08/2017”) or use =day(A6)
Month Function in Google Sheets
Use this function to get the month from a given date. For example consider the given date as 20/08/2017. When you use this month function you will get the result 8. You can either use;
=month(“20/08/2017”) or use =month(A6)
We learned the required date functions to convert date to string in Google Sheets using the long-winded approach.
Now one text function is left. I’m skipping it here. I will explain it later part of this tutorial where we use the date to text conversion. I think that will be easy for you to understand.
Final Step to Convert Date to String Using the Long-winded Approach
We are converting the date to text using the above method to “yyyy-mm-dd” format which is used in Query function. You can tweak the function to “dd-mm-yyyy” format also but not suggestible with Query.
Suppose cell A6 contain a date 20/08/2017, the below formula will convert it to the text string as 2017-08-20.
=text(year(A6),”0000″) &”-“& text(month(A6),”00″) &”-“& text(day(A6),”00″)
Now you should know the text function, which I skipped above, in order to understand the above formula. In our above formula we used multiple text functions.
All the the three date functions in the formula we already explained above. Below is the syntax of the text function.
Now see the first part of our above formula which is as below.
What this text formula will do?
The year part of the formula, year(A6) will extract the year from the date in cell A6. The text function will then convert this number to a four character string. The four zeroes used to format the number to four characters. The text function is that much simple.
Similarly month and date extracted from date as string and joined by the ampersand (&) symbol. The date in A6 is 20/08/2017. Now the formula converted it to 2017-08-20, as a string.
We have learned to convert date to string in Google Sheets using the long-winded approach. This method of date to string conversion is suggested in Query formula.
The above formula can be slightly changed to get the string in dd/mm/yyyy format. See it below. This’s just for your information.
=text(day(A6),“00”) &“/”& text(month(A6),“00”) &“/”& text(YEAR(A6),“0000”)
Hope you understood the use of text function in Google Sheets. Now to the compact method of conversion.
Convert Date to String in Google Sheets Using the Compact Method
It’s quite easy to convert date to string using compact method in Google Sheets. First see the formula below.
The text function will convert the date in cell A6 to yyyy-mm-dd format. To convert the date to string in dd/mm/yyy use the formula as below.
Convert Date to String in Google Sheets Using the Long-winded Approach – Why We Should Follow This Method?
By following the long-winded approach you will get the perfect date conversion as no regional or other date format will affect the result. This is because we extract date, year, and month individually from a given date then go for converting it to text string. I always suggest this way of date to string conversion in Google Sheets.
Hope you understood how to convert date to string using the long-winded approach. Find time to learn this date to text conversion as I may use it in future tutorials where Query function will dominate.