TEXT formatting for dates in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to format the date in cell A1 as "MM/DD/YYYY".

Google Sheets

=TEXT(A1, "[1]")

Drag options to blanks, or click blank then click option'

AMM/DD/YYYY

BYYYY/MM/DD

CDD-MM-YYYY

DHH:MM:SS

Attempts:

3 left

💡 Hint

Common Mistakes

Using dashes instead of slashes

Using lowercase letters which are not recognized

Confusing month and day order

The format "MM/DD/YYYY" shows the month, day, and year in that order, separated by slashes.

2fill in blank

medium

Complete the formula to display the date in cell B2 as "Friday, January 1, 2021".

Google Sheets

=TEXT(B2, "[1]")

Drag options to blanks, or click blank then click option'

Ayyyy-mm-dd

Bdddd, mmmm d, yyyy

Cdd/mm/yyyy

Dddd, mm dd, yyyy

Attempts:

3 left

💡 Hint

Common Mistakes

Using "ddd" which shows abbreviated weekday

Using "mm" which shows month as number

Using day with leading zero

"dddd" shows the full weekday name, "mmmm" the full month name, "d" the day without leading zero, and "yyyy" the full year.

3fill in blank

hard

Fix the error in the formula to show the date in C3 as "Jan 01, 2021".

Google Sheets

=TEXT(C3, "[1]")

Drag options to blanks, or click blank then click option'

Ammm dd, yyyy

Bmmmm dd yyyy

Cmm dd, yyyy

Ddd mmm yyyy

Attempts:

3 left

💡 Hint

Common Mistakes

Using "mmmm" which shows full month name

Missing commas in the format

Using "mm" which shows month as number

"mmm" shows the abbreviated month name, "dd" the day with leading zero, and "yyyy" the full year with commas as shown.

4fill in blank

hard

Fill both blanks to format the date in D4 as "2021-01-01 14:30" (24-hour time).

Google Sheets

=TEXT(D4, "[1] [2]")

Drag options to blanks, or click blank then click option'

Ayyyy-mm-dd

Bhh:mm AM/PM

CHH:mm

Dmm/dd/yyyy

Attempts:

3 left

💡 Hint

Common Mistakes

Using "hh" which is 12-hour format

Using slashes instead of dashes in date

Including AM/PM when 24-hour time is needed

"yyyy-mm-dd" formats the date with dashes, and "HH:mm" formats the time in 24-hour format without AM/PM.

5fill in blank

hard

Fill all three blanks to format the date in E5 as "Saturday, 01-Jan-21".

Google Sheets

=TEXT(E5, "[1], [2]-[3]-yy")

Drag options to blanks, or click blank then click option'

Adddd

Bdd

Cmmm

Dmm

Attempts:

3 left

💡 Hint

Common Mistakes

Using "ddd" which shows abbreviated weekday

Using "mm" instead of "mmm" for month

Using "yyyy" instead of "yy" for two-digit year

"dddd" shows full weekday name, "dd" shows day with leading zero, and "mmm" shows abbreviated month name. The year is shown as two digits with "yy".

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