π Fix Date, Time, and Number Formatting Issues Instantly with the TEXT Function
Have you ever faced these problems when preparing a report?
- Dates appear as serial numbers like 45678 instead of 2025-08-12.
- No thousand separators in large numbers (1200000 instead of 1,200,000).
- Time format breaks, showing 9:5 instead of 09:05.
- Number or date formats become inconsistent in mail merge or automated messages.
In Google Sheets, the most reliable solution is the TEXT function. It converts a value into a string in the format you want, making it powerful for inserting dates, times, and numbers into sentences, or for enforcing consistent display rules in reports, invoices, and dashboards.
β Function Overview
| Function Name | TEXT |
| Purpose | Convert numbers/dates/times into text with a specific format |
| Syntax | =TEXT(value, “format_code”) |
| Return | Text (string) |
β Example 1: Display date as βYYYY.MM.DD (Day)β
=TEXT(A1, "yyyy.mm.dd (ddd)")| A | B | |
| 1 | 2025-08-12 | |
| 2 | =TEXT(A1, “yyyy.mm.dd (ddd)”) |
π When A1 is a valid date, TEXT displays it as β2025.08.12 (Tue)β.
β Example 2: Currency, thousand separator, decimal places
=TEXT(B1, "#,##0.00")
=TEXT(B1, "β©#,##0")
=TEXT(B1, "$#,##0.00")| B | C | |
| 1 | 1200000 | |
| 2 | =TEXT(B1, “#,##0.00”) | |
| 3 | =TEXT(B1, “β©#,##0”) |
π Essential for professional documents like reports, invoices, and quotations.
β Example 3: Time with leading zero, AM/PM notation
=TEXT(C1, "hh:mm")
=TEXT(C1, "hh:mm AM/PM")
=TEXT(C1, "[h]:mm")| C | D | |
| 1 | 09:05 | |
| 2 | =TEXT(C1, “hh:mm”) | |
| 3 | =TEXT(C1, “hh:mm AM/PM”) |
π Keeps time formats consistent and supports AM/PM notation.
β Example 4: Preserve leading zeros
=TEXT(D1, "00000")| D | E | |
| 1 | 42 | |
| 2 | =TEXT(D1, “00000”) |
π Perfect for postal codes, employee IDs, product codes.
β Example 5: Automated sentence generation
="Hello, " & A1 & ". Your payment date is " & TEXT(B1, "yyyy-mm-dd") & " and the amount is " & TEXT(C1, "$#,##0") & "."| A | B | C | D | |
| 1 | John Smith | 2025-08-12 | 1200000 | |
| 2 | =”Hello, ” & A1 & “. Your payment date is ” & TEXT(B1, “yyyy-mm-dd”) & ” and the amount is ” & TEXT(C1, “$#,##0”) & “.” |
π Use TEXT to format dates and amounts before merging into sentences.
πΌ Practical Tips
- Enforce consistent formats in reports
- Standardize formats in mail merge outputs
- Prevent format issues when sharing with users from different locales
- Generate conditional messages with IF + TEXT
β Summary
The TEXT function is a powerful tool to keep formats consistent across reports and automated workflows. It solves common issues like broken date formats, missing thousand separators, and disappearing leading zeros.
