Why date calculations drive business logic in Google Sheets - Business Case Study

Scenario Mode

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

📋 Request: Your manager wants to understand how quickly orders are fulfilled and identify any delays in shipping.

📊 Data: You have a table with Order ID, Order Date, Ship Date, and Delivery Status for recent orders.

🎯 Deliverable: Create a report that calculates the number of days taken to ship each order and highlights orders shipped late (more than 3 days after order date).

Sample Data

Order ID	Order Date	Ship Date	Delivery Status
1001	2024-05-01	2024-05-03	Delivered
1002	2024-05-02	2024-05-06	Delivered
1003	2024-05-03	2024-05-04	Delivered
1004	2024-05-04	2024-05-10	Delivered
1005	2024-05-05	2024-05-07	Delivered
1006	2024-05-06	2024-05-06	Delivered
1007	2024-05-07	2024-05-11	Delivered
1008	2024-05-08	2024-05-09	Delivered

1

Step 1: Add a new column named 'Days to Ship' next to 'Ship Date'.

In the first cell under 'Days to Ship' (e.g., D2), enter the formula: =C2 - B2

Expected Result

For Order ID 1001, the result is 2 (days between 2024-05-01 and 2024-05-03).

2

Step 2: Copy the 'Days to Ship' formula down for all orders.

Drag the fill handle from D2 down to D9 to apply the formula to all rows.

Expected Result

Each order shows the number of days taken to ship.

3

Step 3: Add a new column named 'Late Shipment' to identify orders shipped late (more than 3 days).

In the first cell under 'Late Shipment' (e.g., E2), enter the formula: =IF(D2 > 3, "Yes", "No")

Expected Result

For Order ID 1002, the result is 'Yes' because it took 4 days to ship.

4

Step 4: Copy the 'Late Shipment' formula down for all orders.

Drag the fill handle from E2 down to E9 to apply the formula to all rows.

Expected Result

Orders with shipping time more than 3 days are marked 'Yes', others 'No'.

5

Step 5: Apply conditional formatting to highlight late shipments in the 'Late Shipment' column.

Select the 'Late Shipment' cells, then use Format > Conditional formatting with the rule: Text is exactly 'Yes', set fill color to light red.

Expected Result

Late shipments are visually highlighted in red.

Final Result

Order ID | Order Date | Ship Date | Days to Ship | Late Shipment
--------------------------------------------------------------
1001     | 2024-05-01 | 2024-05-03| 2            | No
1002     | 2024-05-02 | 2024-05-06| 4            | Yes
1003     | 2024-05-03 | 2024-05-04| 1            | No
1004     | 2024-05-04 | 2024-05-10| 6            | Yes
1005     | 2024-05-05 | 2024-05-07| 2            | No
1006     | 2024-05-06 | 2024-05-06| 0            | No
1007     | 2024-05-07 | 2024-05-11| 4            | Yes
1008     | 2024-05-08 | 2024-05-09| 1            | No

✓Most orders are shipped within 3 days.

✓Orders 1002, 1004, and 1007 took longer than 3 days to ship and are late.

✓Identifying late shipments helps focus on improving delivery speed.

Bonus Challenge

Calculate the average shipping time for all orders and separately for late shipments.

Show Hint

Use the AVERAGE function with FILTER to calculate average days for all and for late shipments only.