Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to convert a date to text in Excel and Google Sheets
Excel & Google Sheets
=TEXT(A2,"YYYY-MM-DD")Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Enter your date value in A2 (must be a real date — left-aligned if so).
- 2In a result cell, enter =TEXT(A2,"YYYY-MM-DD") for ISO format.
- 3Swap the format code for your target shape: "MM/DD/YYYY" US, "DD/MM/YYYY" UK, "D MMM YYYY" → "7 Mar 2026", "MMMM D, YYYY" → "March 7, 2026", "dddd" → "Saturday".
- 4Concatenate with other strings: ="Report date: "&TEXT(A2,"MMMM D, YYYY") returns "Report date: March 7, 2026".
- 5Press Enter. The result aligns left in the cell, confirming it's now text — not a date you can do math on.
Tips
- Common format codes: "YYYY" 4-digit year, "YY" 2-digit year, "MMMM" full month name, "MMM" abbreviated month, "MM" zero-padded month number, "DD" zero-padded day, "D" day without padding, "dddd" full weekday name, "ddd" short weekday.
- Combine date and time: =TEXT(A2,"YYYY-MM-DD HH:MM") returns "2026-03-07 14:30". "HH" is 24-hour, "hh" is 12-hour (pair with "AM/PM").
- Once converted to text, the value can't be used in date arithmetic. Keep the original date in another column or wrap downstream calls with DATEVALUE: =DATEDIF(DATEVALUE(C2),TODAY(),"D").
- TEXT respects the spreadsheet locale for month and day NAMES (not for the structure). "MMMM" returns "März" on German Windows but "March" on English. To force English regardless, build the string with CHOOSE(MONTH(A2), "Jan", ...).
- Concatenating a raw date without TEXT loses formatting: ="Today: "&TODAY() returns "Today: 45992" because dates are stored as numbers. Always wrap in TEXT first.
- In Google Sheets, TEXT also accepts custom number format codes from the Format menu — anything that works in Format → Number → Custom works as a TEXT format string.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Why does my date appear as a number like 45292 instead of a formatted date?
That's the date serial number — the internal storage format. Either format the source cell as a Date (Ctrl+1 → Date) or wrap in TEXT(A2, "YYYY-MM-DD") to convert it to a readable string. The serial number itself is correct; only the display needs the format applied.
How do I convert a date to text in a specific language?
TEXT uses your spreadsheet locale by default. In Excel, locale follows Windows system settings. In Google Sheets, File → Settings → Locale controls it. To force a specific language regardless of locale, build the string manually with CHOOSE or by joining MONTH/DAY/YEAR with hardcoded labels.
How do I get the date in ISO 8601 (YYYY-MM-DD) format?
=TEXT(A2, "YYYY-MM-DD"). This is the international standard, sorts correctly as text, and is the safest format for data exchange between systems.
How do I include the day of the week (e.g. "Saturday, March 7, 2026")?
=TEXT(A2, "dddd, MMMM D, YYYY"). "dddd" returns the full weekday name; "ddd" returns the 3-letter abbreviation. Locale-sensitive.
How do I convert TEXT date back to a real date?
Use DATEVALUE: =DATEVALUE(B2) where B2 is the text. DATEVALUE parses common date formats and returns a date serial number. Format the result cell as Date to display it normally.
Why doesn't TEXT change the original cell — only shows the result in a new cell?
TEXT is a formula that returns a new value into another cell. To replace the original, copy the TEXT result column, paste it as values (Paste Special → Values) over the original column. Now the original holds text, not dates.
How do I include time only (no date)?
=TEXT(A2, "HH:MM") for 24-hour, =TEXT(A2, "hh:mm AM/PM") for 12-hour. "HH:MM:SS" adds seconds.