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.