TEXT formatting for dates in Google Sheets - Step-by-Step Guide

Introduction

This feature helps you change how dates look in your spreadsheet. Instead of just showing the date as numbers, you can make it show the day name, month name, or a custom style that is easier to read or fits your needs.

When you want to show the day of the week next to a date, like Monday or Friday.

When you need to display a date as month and year only, like Jan 2024.

When you want to convert a date into a text format for reports or labels.

When you want to show dates in a different style than the default, like 04/27/24 or 27-Apr-2024.

When you want to combine a date with other text in one cell, like 'Due date: April 27, 2024'.

Steps

Step 1: Click

- a cell with a date or where you want the formatted date

The cell is selected and ready for input

Step 2: Type

- the formula bar

You start entering the formula

💡 Start with an equal sign (=) to begin the formula

Step 3: Enter

- the formula bar

The formula appears in the cell showing the formatted date

💡 Use the formula =TEXT(date_cell, "format") where date_cell is the cell with the date

Step 4: Replace

- "format" in the formula

The date changes to the style you want

💡 Use format codes like "dddd" for full day name, "mmm" for short month, "yyyy" for full year

Step 5: Press

- Enter key

The formula is applied and the formatted date shows in the cell

Before vs After

Before

Cell A1 shows 44927 (the date number for April 27, 2023) or a default date format like 4/27/2023

After

Cell B1 shows "Thursday" when using =TEXT(A1, "dddd") or "Apr 27, 2023" when using =TEXT(A1, "mmm dd, yyyy")

Settings Reference

Format text pattern

📍 Inside the TEXT formula as the second argument

To control how the date appears as text in the cell

Default: "mm/dd/yyyy"

Common Mistakes

Typing the date directly inside the TEXT formula without quotes

Google Sheets expects a cell reference or a date value, and the format must be a text string in quotes

Use =TEXT(A1, "dddd") where A1 contains the date, and always put the format pattern inside double quotes

Using wrong format codes like "DD" instead of "dd"

Format codes are case sensitive; "DD" is not recognized and will not format correctly

Use lowercase letters for day and month codes, like "dd" for day and "mm" for month

Expecting the cell to remain a date after using TEXT

TEXT converts the date to text, so you cannot do date calculations on the result

Use TEXT only for display purposes; keep original dates in separate cells for calculations

Summary

The TEXT function changes how dates look by turning them into text with your chosen style.

You must use format codes inside quotes to tell Sheets how to show the date.

Remember that the result is text, so it cannot be used for date math.

Practice

1. What does the formula =TEXT(A1, "dd/mm/yyyy") do when A1 contains a date?

easy

A. It changes how the date looks by showing day, month, and year in that order.

B. It converts the date into a number representing days since 1900.

C. It adds the day, month, and year values together as numbers.

D. It changes the date value to text but keeps the original date format.

Solution

1. Step 1: Understand the TEXT function usage

   The TEXT function formats the date in A1 using the pattern "dd/mm/yyyy" which means day/month/year.

2. Step 2: Recognize the output type

   The output is text showing the date in the specified format, not a number or calculation.

3. Final Answer:

   It changes how the date looks by showing day, month, and year in that order. -> Option A

4. Quick Check:

   TEXT formats date display = It changes how the date looks by showing day, month, and year in that order. [OK]

Hint: TEXT changes date appearance without altering the date value [OK]

Common Mistakes:

• Thinking TEXT changes the date value itself
• Confusing date formatting with date calculations
• Assuming output is a number instead of text

2. Which of these is the correct formula to display the date in cell B2 as "March 15, 2024" in Google Sheets?

easy

A. =TEXT(B2, "mm dd yyyy")

B. =TEXT(B2, "MMM-dd-yyyy")

C. =TEXT(B2, "dd/MM/yyyy")

D. =TEXT(B2, "MMMM dd, yyyy")

Solution

1. Step 1: Identify the format for full month name

   "MMMM" shows the full month name like "March"; "dd" is day with two digits; "yyyy" is four-digit year.

2. Step 2: Match the format to the desired output

   "March 15, 2024" matches "MMMM dd, yyyy" exactly.

3. Final Answer:

   =TEXT(B2, "MMMM dd, yyyy") -> Option D

4. Quick Check:

   Full month name format = =TEXT(B2, "MMMM dd, yyyy") [OK]

Hint: "MMMM" means full month name, "MMM" means short month [OK]

Common Mistakes:

• Using "mm" which shows month as number, not name
• Forgetting the comma in the format string
• Using wrong case letters in format codes

3. If cell C3 contains the date 2024-12-01, what will the formula =TEXT(C3, "ddd, mmm d") display?

medium

A. Sun, Dec 1

B. Sunday, December 01

C. Sun, 12 1

D. 12/01/2024

Solution

1. Step 1: Decode the format codes

   "ddd" shows the short day name (Sun), "mmm" shows short month name (Dec), "d" shows day without leading zero (1).

2. Step 2: Apply to the date 2024-12-01

   December 1, 2024 is a Sunday, so output is "Sun, Dec 1".

3. Final Answer:

   Sun, Dec 1 -> Option A

4. Quick Check:

   Short day and month names = Sun, Dec 1 [OK]

Hint: "ddd" = short weekday, "mmm" = short month name [OK]

Common Mistakes:

• Using "ddd" and expecting full day name
• Confusing "d" with "dd" for day formatting
• Assuming output remains a date, not text

4. The formula =TEXT(D4, "mm/dd/yyyy") returns an error. Which fix will correct it?

medium

A. Change the format string to "MM/DD/YYYY"

B. Remove the quotes around the format string

C. Ensure D4 contains a valid date value

D. Use =TEXT(D4, mm/dd/yyyy) without quotes

Solution

1. Step 1: Check formula syntax

   The formula syntax is correct with quotes around the format string and correct format codes (lowercase mm, dd, yyyy).

2. Step 2: Verify cell content

   If D4 does not contain a valid date, TEXT will error. Fix is to ensure D4 has a proper date value.

3. Final Answer:

   Ensure D4 contains a valid date value -> Option C

4. Quick Check:

   Valid date input needed = Ensure D4 contains a valid date value [OK]

Hint: TEXT needs a valid date input, not text or empty [OK]

Common Mistakes:

• Removing quotes around format string causing syntax error
• Changing format codes to uppercase incorrectly
• Ignoring invalid or empty cell input

5. You want to create a list of dates in column A and show their weekday names in column B using TEXT. Which formula in B2 correctly shows the full weekday name for the date in A2?

hard

A. =TEXT(A2, "ddd")

B. =TEXT(A2, "dddd")

C. =TEXT(A2, "wwww")

D. =TEXT(A2, "day")

Solution

1. Step 1: Understand weekday format codes

   "ddd" gives short weekday name (Mon), "dddd" gives full weekday name (Monday).

2. Step 2: Choose the code for full weekday name

   To show full weekday name, use "dddd" in the TEXT formula.

3. Final Answer:

   =TEXT(A2, "dddd") -> Option B

4. Quick Check:

   Full weekday name needs "dddd" = =TEXT(A2, "dddd") [OK]

Hint: "dddd" = full weekday name, "ddd" = short weekday [OK]

Common Mistakes:

• Using "ddd" and expecting full weekday name
• Using invalid format codes like "wwww" or "day"
• Forgetting quotes around format string