TEXT formatting for dates in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the TEXT function do when formatting dates in Excel?

The TEXT function converts a date into text in a specific format you choose, like "dd/mm/yyyy" or "mmmm dd, yyyy". It helps show dates exactly how you want them.

beginner

How would you format the date in cell A1 to show as "March 15, 2024" using TEXT?

Use the formula =TEXT(A1, "mmmm dd, yyyy"). This changes the date to full month name, day with two digits, and four-digit year.

beginner

What does the format code "dd/mm/yyyy" mean in the TEXT function?

"dd" means day with two digits, "mm" means month with two digits, and "yyyy" means four-digit year. So a date like March 5, 2024 becomes 05/03/2024.

intermediate

Why use TEXT to format dates instead of changing cell format?

TEXT creates a text string with the date in the format you want, which you can combine with other text or formulas. Changing cell format only changes how the date looks but keeps it as a date value.

intermediate

What happens if you use TEXT on a date but give a wrong format code?

Excel may show an error or unexpected text. The format codes must be correct for dates, like "dd", "mm", "yyyy". Wrong codes won't format the date properly.

Which formula shows the date in cell A1 as "15-Mar-2024"?

A =TEXT(A1, "dd-mmm-yyyy")

B =TEXT(A1, "mm/dd/yyyy")

C =TEXT(A1, "yyyy-mm-dd")

D =TEXT(A1, "mmmm dd, yyyy")

The format "dd-mmm-yyyy" shows day as two digits, month as three-letter abbreviation, and four-digit year.

What does the format code "mmmm" show in the TEXT function?

A Month as number

B Month as full name

C Month as three-letter abbreviation

D Minute in time

"mmmm" shows the full month name like "January", "February", etc.

If A1 contains a date, what does =TEXT(A1, "yyyy/mm/dd") return?

A Year/Month/Day as text

B Date value unchanged

C Date in default format

D Error

TEXT converts the date to text in the format year/month/day.

Why might you use TEXT to format a date inside a sentence?

A To keep the date as a number

B To combine the date with other text

C To change the cell color

D To sort dates

TEXT lets you turn a date into text so you can join it with other words in a sentence.

What is the result of =TEXT(A1, "dd/mm/yy") if A1 is March 5, 2024?

A 05/03/24

B 5/3/2024

C 03/05/2024

D March 5, 2024

"dd/mm/yy" shows day and month as two digits and year as two digits.

Explain how to use the TEXT function to format a date in Excel.

Think about how TEXT changes date appearance and why you might want text output.

List common date format codes used inside the TEXT function and what they mean.

Focus on day, month, and year codes.