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 keeping the underlying datevalue in Google Sheets.

Your search to format date using a formula usually end up in a solution using the TEXT function.

Is there any alternative to the TEXT function in Google Sheets to format a date?

Yes! I’ll come to that. Let’s first see why the TEXT function is not always recommendable.

It has an ‘issue’ that you may have already come across. Let’s test that and proceed further.

In the following example, we will use the TEXT function to format a date in the format “DD/MM/YYYY” into “DD-MMM-YYYY”:

Cell C2 contains the date “26/01/2023” without double quotes. It is an original date and in a recognizable date 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 TEXT Formatted date in cell D2.

Here is the test result.

See the last formula, which returns FALSE instead of TRUE!

Even though 31/12/2025 is greater than 26/01/2023, the formula returns FALSE.

You may face issues when using TEXT formatted date within functions such as ISBETWEEN, FILTER, etc., or wherever comparison operators are involved.

Here is one more example before we attempt to format a date using a formula without converting it into text.

I’ve used =text(C2,"DD-MMM-YYYY") in D2 and =text(C3,"DD-MMM-YYYY") in D3 to format the dates in C2 and C3, respectively.

The E2 formula doesn’t recognize these formatted dates.

Formula # 1:

=filter(B2:B11,isbetween(A2:A11,D2,D3))

I expect it to return “E,” “F,” “G,” and “H,” i.e., the values in the highlighted cells.

The following FILTER formula, which uses the original dates, does that.

Formula # 2:

=filter(B2:B11,isbetween(A2:A11,C2,C3))

We can format a date using a formula and keep the underlying date value in Google Sheets. Here is how.

Formatting a Date Using a Formula Without Converting it Into a Text

The answer is QUERY.

Yep! We can use the FORMAT clause in the QUERY function to format a date without converting it into text in Google Sheets.

If you take the above last example, where the formula # 1 returns an #N/A, we can apply the QUERY as follows.

In D2 enter =query(C2,"format C 'DD-MMM-YYYY'") and D3 =query(C3,"format C 'DD-MMM-YYYY'").

Formula # 1 will start working correctly because the QUERY formulas format the dates keeping the underlying date values.

I hope the above answers how to format a date using a formula without converting it into text.

What about formatting all the dates in column A, keeping the underlying date value?

Here is the formula for that.

=query(A2:A,"Format A 'DD-MMM-YYYY'")

You May Also Like: How to Format Date, Time, and Number in Google Sheets Query.