TEXT formatting for dates in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Format date as full weekday name

You have a date in cell A1: 2024-06-15. Which formula will display the full weekday name (e.g., "Saturday")?

A =TEXT(A1, "ddd")

B =TEXT(A1, "dddd")

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

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

💡 Hint

Use the TEXT function with the right format code for full weekday name.

The format code dddd shows the full weekday name like "Saturday". ddd shows abbreviated weekday, and the others show date formats.

📊 Formula Result

intermediate

Format date as day-month-year with short month name

Given a date in cell B2: 2024-12-01, which formula formats it as "01-Dec-2024"?

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

B =TEXT(B2, "mm-dd-yyyy")

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

D =TEXT(B2, "yyyy-mmm-dd")

💡 Hint

Use the format code for day, short month name, and year.

dd is day with leading zero, mmm is short month name, and yyyy is full year.

❓ Function Choice

advanced

Choose formula to display date as "Month day, year"

You want to show the date in cell C3 as "June 5, 2024". Which formula will do this correctly?

A =TEXT(C3, "mmm dd yyyy")

B =TEXT(C3, "mm dd, yyyy")

C =TEXT(C3, "m/d/yyyy")

D =TEXT(C3, "mmmm d, yyyy")

💡 Hint

Look for full month name and day without leading zero.

mmmm gives full month name, d gives day without leading zero, and yyyy is year.

📊 Formula Result

advanced

Format date to show ISO week number

Given a date in D4, which formula shows the ISO week number (1 to 53) of that date?

A =ISOWEEKNUM(D4)

B =WEEKNUM(D4, 2)

C =TEXT(D4, "ww")

D =WEEKDAY(D4)

💡 Hint

ISO week number starts on Monday and can be different from WEEKNUM.

ISOWEEKNUM returns the ISO standard week number. WEEKNUM counts weeks differently. TEXT with "ww" is not a valid date format code for week number.

❓ data_analysis

expert

Count dates formatted as "MMM-YY" in a range

You have dates in cells A1:A10. You want to count how many dates appear as "Jun-24" when formatted with =TEXT(date, "MMM-YY"). Which formula correctly counts these dates?

A =COUNTIF(ARRAYFORMULA(TEXT(A1:A10, "MMM-YY")), "Jun-24")

B =COUNTIF(A1:A10, "Jun-24")

C =SUMPRODUCT(--(TEXT(A1:A10, "MMM-YY") = "Jun-24"))

D =COUNTIF(A1:A10, TEXT("Jun-24", "MMM-YY"))

💡 Hint

You need to convert dates to text first, then compare.

SUMPRODUCT(--(TEXT(A1:A10, "MMM-YY") = "Jun-24")) counts how many dates match the text "Jun-24". COUNTIF alone won't work because it compares raw dates, not formatted text.