TEXT formatting for dates in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Format a date as 'Day-Month-Year' text

You have a date in cell A1: 2024-06-15. Which formula correctly converts this date to the text format 15-Jun-2024?

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

B TEXT(A1, "mm-dd-yyyy")

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

D TEXT(A1, "dd/mm/yy")

💡 Hint

Use the TEXT function with the correct date format codes for day, month abbreviation, and year.

The format code "dd-mmm-yyyy" shows day as two digits, month as three-letter abbreviation, and full year. Other options produce different formats.

📊 Formula Result

intermediate

Display weekday name from a date

Given a date in cell B2 (e.g., 2024-06-18), which formula returns the full weekday name like Tuesday?

A TEXT(B2, "dd")

B TEXT(B2, "ddd")

C TEXT(B2, "dddd")

D TEXT(B2, "mm")

💡 Hint

Use the format code for full weekday name in the TEXT function.

"dddd" returns the full weekday name (e.g., Tuesday). "ddd" returns abbreviated weekday (Tue). "dd" returns day number, "mm" returns month number.

❓ Function Choice

advanced

Choose formula to format date as 'Month day, Year' text

You want to convert a date in cell C3 to text like June 5, 2024. Which formula does this correctly?

A TEXT(C3, "mm dd, yyyy")

B TEXT(C3, "mmmm d, yyyy")

C TEXT(C3, "mmm dd yyyy")

D TEXT(C3, "dd mmmm yyyy")

💡 Hint

Use full month name, day without leading zero, and full year format codes.

"mmmm" gives full month name, "d" gives day without leading zero, and "yyyy" gives full year. Other options produce wrong formats or missing commas.

📊 Formula Result

advanced

Format date with leading zero day and abbreviated month

Cell D4 contains the date 2024-06-07. Which formula returns 07-Jun-24 as text?

A TEXT(D4, "dd-mmm-yy")

B TEXT(D4, "d-mmm-yyyy")

C TEXT(D4, "d-mmm-yy")

D TEXT(D4, "dd-mm-yy")

💡 Hint

Use two-digit day, three-letter month abbreviation, and two-digit year format codes.

"dd" ensures day has leading zero, "mmm" is abbreviated month, "yy" is two-digit year. Other options miss leading zero or use wrong month format.

🎯 Scenario

expert

Combine date and time formatting in one TEXT formula

Cell E5 has a date and time value: 2024-06-15 14:30. Which formula converts it to text 15-Jun-2024 14:30?

A TEXT(E5, "dd-mmm-yyyy hh:mm")

B TEXT(E5, "dd-mmm-yyyy hh:mm AM/PM")

C TEXT(E5, "dd/mm/yyyy HH:mm")

D TEXT(E5, "dd-mmm-yyyy HH:mm")

💡 Hint

Use 24-hour time format without AM/PM and correct date format.

"HH:mm" gives 24-hour time with leading zero hour and minutes. "hh:mm AM/PM" uses 12-hour time with AM/PM, which is not wanted here. Other options use wrong separators or time formats.