TEXT formatting for dates in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

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

Excel

=TEXT(A1, "[1]")

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

ADD-MM-YYYY

BMM/DD/YYYY

CYYYY/MM/DD

DHH:MM:SS

Attempts:

3 left

💡 Hint

Common Mistakes

Using time formats like "HH:MM:SS" instead of date formats.

Mixing up day and month order.

The format code "MM/DD/YYYY" shows the date as month/day/year, which is the requested format.

2fill in blank

medium

Complete the formula to display the date in cell B2 as the full month name, day, and year (e.g., "January 15, 2024").

Excel

=TEXT(B2, "[1]")

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

AMMM DD, YYYY

BMM/DD/YYYY

CDD-MM-YYYY

DMMMM DD, YYYY

Attempts:

3 left

💡 Hint

Common Mistakes

Using "MMM" instead of "MMMM" for full month name.

Omitting the comma after the day.

"MMMM" shows the full month name, "DD" the day, and "YYYY" the year, matching the example format.

3fill in blank

hard

Fix the error in the formula to show the date in C3 as "Day, Month DD, YYYY" (e.g., "Monday, January 15, 2024").

Excel

=TEXT(C3, "[1]")

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

ADDD, MMMM DD, YYYY

BDDDD, MMMM DD, YYYY

Cdddd, MMMM DD, YYYY

Ddddd, MMM DD, YYYY

Attempts:

3 left

💡 Hint

Common Mistakes

Using uppercase or incorrect day format codes.

Using short month name instead of full month name.

In Excel, "dddd" gives the full weekday name. "DDDD" or "DDD" are not valid format codes.

4fill in blank

hard

Fill both blanks to format the date in D4 as "YY/MM/DD" and include the weekday abbreviation (e.g., "24/01/15 Mon").

Excel

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

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

AYY/MM/DD

Bddd

CDDD

DDD-MM-YY

Attempts:

3 left

💡 Hint

Common Mistakes

Using "DDD" which is not a valid weekday abbreviation in Excel.

Mixing month and weekday codes.

"YY/MM/DD" formats the date as two-digit year/month/day. "ddd" gives the three-letter weekday abbreviation.

5fill in blank

hard

Fill all three blanks to create a formula that formats the date in E5 as "Month DD, YYYY at HH:MM AM/PM" (e.g., "January 15, 2024 at 03:30 PM").

Excel

=TEXT(E5, "[1]") & " at " & TEXT(E5, "[2]") & " [3]"

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

AMMMM DD, YYYY

Bhh:mm

CAM/PM

DHH:MM

Attempts:

3 left

💡 Hint

Common Mistakes

Using "HH" for 24-hour format instead of "hh" for 12-hour.

Using lowercase "am/pm" which Excel does not recognize.

"MMMM DD, YYYY" formats the full month, day, and year. "hh:mm" shows hour and minute in 12-hour format. "AM/PM" adds the meridian indicator.