TEXT formatting for dates in Google Sheets - 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 a clear, readable format for a presentation.

📊 Data: You have a list of sales transactions with dates in the default date format and sales amounts.

🎯 Deliverable: Create a new column that shows the sales dates formatted as 'Day, Month Date, Year' (for example, 'Monday, January 1, 2024').

Sample Data

Transaction ID	Sale Date	Amount
1001	2024-01-01	250
1002	2024-01-05	450
1003	2024-01-10	300
1004	2024-01-15	500
1005	2024-01-20	350
1006	2024-01-25	400
1007	2024-01-30	600

1

Step 1: Insert a new column next to the 'Sale Date' column and name it 'Formatted Date'.

Expected Result

A new empty column labeled 'Formatted Date' appears next to 'Sale Date'.

2

Step 2: In the first cell under 'Formatted Date' (assume cell C2), enter the formula to format the date in B2 as 'Day, Month Date, Year'.

=TEXT(B2, "dddd, mmmm d, yyyy")

Expected Result

The date '2024-01-01' in B2 displays as 'Monday, January 1, 2024' in C2.

3

Step 3: Copy the formula from C2 down the 'Formatted Date' column for all rows with data.

Drag the fill handle from C2 down to C8.

Expected Result

All dates in the 'Formatted Date' column show as full day names, full month names, day number, and year, e.g., 'Friday, January 5, 2024'.

4

Step 4: Check that all formatted dates match the original dates but in the new readable format.

Expected Result

Each formatted date correctly corresponds to its original date in the new format.

Final Result

Transaction ID | Sale Date  | Amount | Formatted Date           
-------------- | ---------- | ------ | ------------------------
1001           | 2024-01-01 | 250    | Monday, January 1, 2024  
1002           | 2024-01-05 | 450    | Friday, January 5, 2024  
1003           | 2024-01-10 | 300    | Wednesday, January 10, 2024
1004           | 2024-01-15 | 500    | Monday, January 15, 2024 
1005           | 2024-01-20 | 350    | Saturday, January 20, 2024
1006           | 2024-01-25 | 400    | Thursday, January 25, 2024
1007           | 2024-01-30 | 600    | Tuesday, January 30, 2024

✓The dates are now easy to read with full day and month names.

✓This format helps the manager quickly understand when sales occurred.

✓Using the TEXT function in Google Sheets is a simple way to change date appearance without changing the actual date data.

Bonus Challenge

Format the dates to show only the month and year, like 'January 2024', in a new column.

Show Hint

Use the formula =TEXT(B2, "mmmm yyyy") in the new column.