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.

Practice

1. Why is it important to use date calculations in business spreadsheets?

easy

A. They are only useful for birthdays.

B. They help track deadlines and calculate durations automatically.

C. They slow down the spreadsheet performance.

D. They make the spreadsheet look colorful.

Solution

1. Step 1: Understand the role of dates in business

   Businesses often need to track deadlines, project durations, and schedules.

2. Step 2: Recognize how date calculations help

   Date calculations automate tracking and reduce manual errors, making management easier.

3. Final Answer:

   They help track deadlines and calculate durations automatically. -> Option B

4. Quick Check:

   Date calculations = track deadlines [OK]

Hint: Think about deadlines and schedules in daily work [OK]

Common Mistakes:

• Thinking dates only add colors
• Believing dates slow spreadsheets
• Assuming dates are for birthdays only

2. Which formula correctly calculates the number of days between two dates in Google Sheets?

easy

A. =DATEDIF(A1, B1, "D")

B. =SUM(A1, B1)

C. =A1+B1

D. =DATE(A1, B1)

Solution

1. Step 1: Identify the function for date difference

   The DATEDIF function calculates the difference between two dates in specified units.

2. Step 2: Check the syntax for days

   Using "D" as the third argument returns the difference in days.

3. Final Answer:

   =DATEDIF(A1, B1, "D") -> Option A

4. Quick Check:

   DATEDIF with "D" = days difference [OK]

Hint: Use DATEDIF with "D" to get days difference [OK]

Common Mistakes:

• Using SUM to add dates
• Adding dates directly with +
• Using DATE function incorrectly

3. Given the formula =TODAY()-A1 where A1 contains the date 2024-06-01 and today is 2024-06-10, what is the result?

medium

A. 10

B. Error

C. 1

D. 9

Solution

1. Step 1: Understand the TODAY() function

   TODAY() returns the current date, which is 2024-06-10 in this case.

2. Step 2: Calculate the difference

   Subtracting 2024-06-01 from 2024-06-10 gives 9 days.

3. Final Answer:

   9 -> Option D

4. Quick Check:

   2024-06-10 - 2024-06-01 = 9 [OK]

Hint: Subtract older date from newer date for days difference [OK]

Common Mistakes:

• Counting inclusive days incorrectly
• Assuming TODAY() returns date-time
• Expecting error from date subtraction

4. A user tries to calculate days between dates with =DATEDIF(A1, B1, "M") but gets an error. What is the likely cause?

medium

A. DATEDIF does not support "M" as unit.

B. The formula syntax is incorrect.

C. B1 date is earlier than A1 date.

D. Dates are not formatted as text.

Solution

1. Step 1: Check DATEDIF requirements

   DATEDIF requires the start date (A1) to be earlier or equal to the end date (B1).

2. Step 2: Understand error cause

   If B1 is earlier than A1, DATEDIF returns an error even if syntax is correct.

3. Final Answer:

   B1 date is earlier than A1 date. -> Option C

4. Quick Check:

   Start date must be before end date [OK]

Hint: Ensure start date ≤ end date in DATEDIF [OK]

Common Mistakes:

• Thinking "M" is invalid unit
• Assuming syntax error causes problem
• Not checking date order

5. You want to create a dynamic report that shows how many days remain until a project deadline in cell B2. Which formula should you use in cell C2 to update automatically each day?

hard

A. =B2 - TODAY()

B. =TODAY() - B2

C. =DATEDIF(TODAY(), B2, "Y")

D. =SUM(B2, TODAY())

Solution

1. Step 1: Understand the goal

   The formula should calculate days remaining, so deadline minus today.

2. Step 2: Choose correct formula

   =B2 - TODAY() subtracts current date from deadline, updating daily.

3. Step 3: Check other options

   Reversing the subtraction (=TODAY() - B2) shows past days, DATEDIF with "Y" calculates years instead of days, and SUM adds dates (invalid).

4. Final Answer:

   =B2 - TODAY() -> Option A

5. Quick Check:

   Deadline - Today = days remaining [OK]

Hint: Subtract TODAY() from deadline for days left [OK]

Common Mistakes:

• Reversing date subtraction
• Using years instead of days
• Adding dates instead of subtracting