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.

Practice

1. What does the Excel formula =TEXT(A1, "dd/mm/yyyy") do when A1 contains a date?

easy

A. It adds the text "dd/mm/yyyy" after the date.

B. It converts the date into a number representing days since 1900.

C. It changes how the date looks to day/month/year format without changing the date value.

D. It changes the date to text but keeps the original date format.

Solution

1. Step 1: Understand the TEXT function usage

   The TEXT function formats a value (like a date) into text using the specified format string.

2. Step 2: Analyze the format string "dd/mm/yyyy"

   This format shows the day as two digits, month as two digits, and year as four digits, separated by slashes.

3. Final Answer:

   It changes how the date looks to day/month/year format without changing the date value. -> Option C

4. Quick Check:

   TEXT formats date display without altering date value [OK]

Hint: TEXT formats dates visually without changing the date itself [OK]

Common Mistakes:

• Thinking TEXT changes the actual date value
• Confusing format codes with literal text
• Assuming TEXT adds extra text after the date

2. Which of these formulas correctly formats the date in cell B2 to show the full month name, day, and year like "March 15, 2024"?

easy

A. =TEXT(B2, "mm dd yyyy")

B. =TEXT(B2, "dd mmmm yyyy")

C. =TEXT(B2, "MMM dd yyyy")

D. =TEXT(B2, "mmmm dd, yyyy")

Solution

1. Step 1: Identify the format for full month name

   In Excel, "mmmm" shows the full month name (e.g., March).

2. Step 2: Check the full format string

   "mmmm dd, yyyy" formats as full month name, day with two digits, comma, and four-digit year.

3. Final Answer:

   =TEXT(B2, "mmmm dd, yyyy") -> Option D

4. Quick Check:

   Full month name uses "mmmm" in TEXT format [OK]

Hint: Use "mmmm" for full month name in TEXT format [OK]

Common Mistakes:

• Using "mm" which shows month as number
• Forgetting the comma after day
• Using "MMM" which shows abbreviated month

3. If cell C3 contains the date 2024-07-04, what will the formula =TEXT(C3, "ddd, mmm d") display?

medium

A. Thursday, Jul 4

B. Thu, Jul 4

C. 07, 04

D. Thursday, July 04

Solution

1. Step 1: Understand "ddd" and "mmm" codes

   "ddd" shows abbreviated weekday (Thu), "mmm" shows abbreviated month (Jul).

2. Step 2: Analyze the full format "ddd, mmm d"

   This shows abbreviated weekday, comma, abbreviated month, and day without leading zero (4).

3. Final Answer:

   Thu, Jul 4 -> Option B

4. Quick Check:

   Abbreviated day and month use "ddd" and "mmm" [OK]

Hint: Use "ddd" and "mmm" for short day and month names [OK]

Common Mistakes:

• Confusing "ddd" with full weekday name
• Expecting leading zero on day with single "d"
• Using "dddd" or "mmmm" incorrectly

4. You want to format a date in cell D5 as "2024/03/09" using TEXT, but your formula =TEXT(D5, "yyyy-mm-dd") shows "2024-03-09" instead. What is the fix?

medium

A. Change the format to "yyyy/mm/dd"

B. Replace "mm" with "MM"

C. Use "yyyy\mm\dd" to escape slashes

D. No fix needed; output is correct

Solution

1. Step 1: Identify the separator issue

   The formula uses dashes (-) but you want slashes (/).

2. Step 2: Replace dashes with slashes in format string

   Change "yyyy-mm-dd" to "yyyy/mm/dd" to get slashes in output.

3. Final Answer:

   Change the format to "yyyy/mm/dd" -> Option A

4. Quick Check:

   Use slashes in format string to show slashes in output [OK]

Hint: Use slashes in format string to get slashes in output [OK]

Common Mistakes:

• Using uppercase "MM" which is invalid for months
• Trying to escape slashes unnecessarily
• Thinking output changes without changing format string

5. You have a date in cell E7 and want to create a custom text showing "Day: 07, Month: Mar, Year: 2024" using TEXT. Which formula achieves this?

hard

A. =TEXT(E7, "'Day:' dd, 'Month:' mmm, 'Year:' yyyy")

B. =TEXT(E7, "\Day: dd, \Month: mmm, \Year: yyyy")

C. =TEXT(E7, "Day: dd, Month: mmm, Year: yyyy")

D. =TEXT(E7, "Day: " & dd & ", Month: " & mmm & ", Year: " & yyyy)

Solution

1. Step 1: Understand how to include text in format string

   Text inside quotes must be enclosed in single quotes within the format string to show literally.

2. Step 2: Analyze each option

   =TEXT(E7, "'Day:' dd, 'Month:' mmm, 'Year:' yyyy") correctly uses single quotes around text parts to display them literally with date codes.

3. Final Answer:

   =TEXT(E7, "'Day:' dd, 'Month:' mmm, 'Year:' yyyy") -> Option A

4. Quick Check:

   Use single quotes to include literal text in TEXT format [OK]

Hint: Put literal text in single quotes inside TEXT format string [OK]

Common Mistakes:

• Not quoting literal text, causing errors
• Using backslashes which don't work in TEXT format
• Trying to concatenate inside format string