SheetSense

Format a date as text with TEXT

TEXT converts a date into text using a format code you supply, so the date can be combined with other words.

fx
=TEXT(A2,"mmm d, yyyy")

The sheet: Column A holds real dates. You want them as readable text such as Mar 5, 2026 to join into a sentence or a label.

How it works, step by step

  1. 01A2 is the date to format.
  2. 02"mmm" is the three-letter month, "d" the day without a leading zero and "yyyy" the four-digit year.
  3. 03The result is text, for example Mar 5, 2026, and can be joined with & such as ="Due "&TEXT(A2,"mmm d").

The same logic in four tools

Excel
=TEXT(A2,"mmm d, yyyy")
Google Sheets
=TEXT(A2,"mmm d, yyyy")
SQL
SELECT to_char(order_date, 'Mon FMDD, YYYY') AS label
FROM orders;  -- PostgreSQL
Python (pandas)
df["label"] = df["order_date"].dt.strftime("%b %d, %Y")

Mistakes that break it

  • !The output is text, not a date. You cannot sort or do date math on it.
  • !In format codes mm after h or ss means minutes, otherwise months. Use mmm or mmmm to be safe.
  • !Format codes depend on the language of the workbook. A German Excel expects JJJJ instead of yyyy.
  • !The pandas %d gives a leading zero (Mar 05). Removing it differs by operating system, so check your platform.

Common questions

How do I show the weekday?
Use "dddd" for the full name, such as Thursday, or "ddd" for Thu.
Why does TEXT show ##### or the code itself?
The code is not valid for your locale, or A2 is text rather than a real date.

Got a version of this with your own ranges?

Paste it into the free translator and get the same breakdown for your exact formula. To run it against a real file, upload the CSV and describe the formula you need in a sentence.