TEXT formatting for dates in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a report showing sales dates in different readable formats for a presentation.

📊 Data: You have a list of sales dates in one column in Excel, stored as date values.

🎯 Deliverable: Create a table showing the original date and three new columns with the dates formatted as 'Month Day, Year', 'Day-Month-Year', and 'Weekday, Month Day'.

Sample Data

Sale Date
2024-01-15
2024-02-28
2024-03-10
2024-04-05
2024-05-22
2024-06-30
2024-07-14
2024-08-01
2024-09-19
2024-10-31

1

Step 1: Enter the sample sales dates in column A starting from cell A2.

Manually input dates or paste the sample data into cells A2 to A11.

Expected Result

Cells A2 to A11 contain valid Excel dates for sales.

2

Step 2: Create a header in cell B1 named 'Format 1: Month Day, Year'.

Type 'Format 1: Month Day, Year' in cell B1.

Expected Result

Cell B1 shows the header text.

3

Step 3: In cell B2, enter a formula to format the date in A2 as 'Month Day, Year' (e.g., January 15, 2024).

=TEXT(A2, "mmmm d, yyyy")

Expected Result

Cell B2 shows 'January 15, 2024'.

4

Step 4: Copy the formula from B2 down to B11 to format all dates in column A.

Drag the fill handle from B2 down to B11.

Expected Result

Cells B2 to B11 show dates formatted like 'February 28, 2024', 'March 10, 2024', etc.

5

Step 5: Create a header in cell C1 named 'Format 2: Day-Month-Year'.

Type 'Format 2: Day-Month-Year' in cell C1.

Expected Result

Cell C1 shows the header text.

6

Step 6: In cell C2, enter a formula to format the date in A2 as 'Day-Month-Year' with two-digit day and month (e.g., 15-01-2024).

=TEXT(A2, "dd-mm-yyyy")

Expected Result

Cell C2 shows '15-01-2024'.

7

Step 7: Copy the formula from C2 down to C11 to format all dates in column A.

Drag the fill handle from C2 down to C11.

Expected Result

Cells C2 to C11 show dates formatted like '28-02-2024', '10-03-2024', etc.

8

Step 8: Create a header in cell D1 named 'Format 3: Weekday, Month Day'.

Type 'Format 3: Weekday, Month Day' in cell D1.

Expected Result

Cell D1 shows the header text.

9

Step 9: In cell D2, enter a formula to format the date in A2 as 'Weekday, Month Day' (e.g., Monday, January 15).

=TEXT(A2, "dddd, mmmm d")

Expected Result

Cell D2 shows 'Monday, January 15'.

10

Step 10: Copy the formula from D2 down to D11 to format all dates in column A.

Drag the fill handle from D2 down to D11.

Expected Result

Cells D2 to D11 show dates formatted like 'Wednesday, February 28', 'Sunday, March 10', etc.

Final Result

Sale Date   | Format 1: Month Day, Year | Format 2: Day-Month-Year | Format 3: Weekday, Month Day
---------------------------------------------------------------------------------------------
2024-01-15  | January 15, 2024          | 15-01-2024               | Monday, January 15
2024-02-28  | February 28, 2024         | 28-02-2024               | Wednesday, February 28
2024-03-10  | March 10, 2024            | 10-03-2024               | Sunday, March 10
2024-04-05  | April 5, 2024             | 05-04-2024               | Friday, April 5
2024-05-22  | May 22, 2024              | 22-05-2024               | Wednesday, May 22
2024-06-30  | June 30, 2024             | 30-06-2024               | Sunday, June 30
2024-07-14  | July 14, 2024             | 14-07-2024               | Sunday, July 14
2024-08-01  | August 1, 2024            | 01-08-2024               | Thursday, August 1
2024-09-19  | September 19, 2024        | 19-09-2024               | Thursday, September 19
2024-10-31  | October 31, 2024          | 31-10-2024               | Thursday, October 31

✓The TEXT function lets you show dates in many readable ways.

✓You can customize date formats to match your report style.

✓Using TEXT keeps the original date value intact for calculations.

Bonus Challenge

Add a new column that shows the quarter of the year for each sale date (e.g., Q1, Q2).

Show Hint

Use the formula ="Q" & ROUNDUP(MONTH(A2)/3, 0) in the new column.

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