Can we format a date from “DD/MM/YYYY” to “DD-MMM-YYYY” using a formula without converting it into text in Google Sheets? My answer is “Yes.” We can format a date using a formula while keeping the underlying date value in Google Sheets.
The Issue with the TEXT Function
Your search for formatting a date using a formula in Google Sheets usually leads to using the TEXT function. However, the TEXT function has an issue that you may have already come across. Let’s test it and see why it’s not always recommendable.
In the following example, we will use the TEXT function to format a date in the “DD/MM/YYYY” format into “DD-MMM-YYYY”:
Cell C2 contains the date “26/01/2023” without double quotes. It is an original date and in a recognizable format.
We can use the following formula in cell D2 to format this date: =text(C2,"DD-MMM-YYYY").
Now let’s test the result of this formula.
Testing the Date Formatted Using the TEXT Function
We have our original date in cell C2 and the TEXT formatted date in cell D2. Here is the test result:
| Formula | Result | Passed/Failed |
|---|---|---|
| =text(C2,”DD-MMM-YYYY”) | 26-Jan-2023 | TRUE |
| =C2=D2 | FALSE | FALSE |
As you can see, the last formula returns FALSE instead of TRUE, even though 31/12/2025 is greater than 26/01/2023. This discrepancy can cause issues when using TEXT formatted dates within functions such as ISBETWEEN, FILTER, etc., or wherever comparison operators are involved.
Formatting a Date Without Converting it Into a Text
But fear not, there is an alternative solution to format a date using a formula without converting it into text – the QUERY function.
Yes! We can use the FORMAT clause in the QUERY function to format a date without converting it into text in Google Sheets.
Let’s take the previous example where formula #1 returned an #N/A, and apply the QUERY function instead:
In D2, enter =query(C2,"format C 'DD-MMM-YYYY'"), and in D3, enter =query(C3,"format C 'DD-MMM-YYYY'").
Now, formula #1 will start working correctly because the QUERY formulas format the dates while keeping the underlying date values.
Formatting All Dates in a Column
What if you want to format all the dates in a column, while keeping the underlying date values? Well, here’s the formula for that:
=query(A2:A,"Format A 'DD-MMM-YYYY'")
By using the QUERY function with the FORMAT clause, you can easily format dates in Google Sheets without converting them into text.
I hope the above answers how to format a date using a formula without converting it into text.
You May Also Like: How to Format Date, Time, and Number in Google Sheets Query.
For more exciting Google Sheets tips and tricks, visit Crawlan.com.
