TEXT formatting for dates in Google Sheets - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the TEXT function do when formatting dates in Google Sheets?

The TEXT function converts a date into text in a specific format you choose, like "MM/DD/YYYY" or "DD-MMM-YYYY". It helps show dates exactly how you want them to appear.

beginner

How would you format a date to show as "April 27, 2024" using the TEXT function?

Use =TEXT(A1, "MMMM D, YYYY") where A1 has the date. "MMMM" shows the full month name, "D" the day without leading zero, and "YYYY" the full year.

beginner

What format code would you use in TEXT to display the day of the week as a short name, like "Mon"?

Use "ddd" in the format code. For example, =TEXT(A1, "ddd") will show the day as "Mon", "Tue", etc.

intermediate

Why might you want to use TEXT formatting for dates instead of just changing the cell format?

TEXT creates a text string with the date in the exact format you want, which is useful when combining dates with other text or when exporting data. Changing cell format only changes how the date looks but keeps it as a date value.

beginner

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

Google Sheets will show an error like #VALUE! because it doesn't understand the format code. You must use valid date format codes like YYYY, MM, DD, MMM, etc.

Which formula correctly formats the date in A1 as "27-Apr-2024"?

A =TEXT(A1, "DD-MMM-YYYY")

B =TEXT(A1, "MM/DD/YYYY")

C =TEXT(A1, "YYYY/MM/DD")

D =TEXT(A1, "MMMM D, YYYY")

The format "DD-MMM-YYYY" shows day as two digits, month as three-letter abbreviation, and full year.

What does the format code "MMMM" display in the TEXT function?

A Full month name

B Month as a number with leading zero

C Three-letter month abbreviation

D Day of the month

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

If you want to show the day of the week as "Monday", which format code should you use?

A D

B ddd

C DD

D dddd

"dddd" shows the full day name like "Monday"; "ddd" shows short name like "Mon".

What type of value does the TEXT function return when formatting a date?

A A date value

B A number

C A text string

D A boolean

TEXT returns a text string formatted as specified, not a date value.

Which of these is NOT a valid date format code in TEXT function?

A YYYY

B ZZZ

C DD

D MM

"ZZZ" is not a recognized date format code in Google Sheets.

Explain how to use the TEXT function to format a date in Google Sheets and why it might be useful.

Think about turning dates into readable text in a specific style.

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

Focus on year, month, and day codes.