Why date calculations drive business logic in Google Sheets - Challenge Your Understanding

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate the number of days between two dates

You have a start date in cell A1 (2024-04-01) and an end date in cell B1 (2024-04-15). Which formula correctly calculates the number of days between these two dates?

A =DATEVALUE(B1) - DATEVALUE(A1)

B =B1 - A1

C =B1 + A1

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

💡 Hint

Use a function designed to calculate the difference between dates in days.

The DATEDIF function with the "D" unit returns the number of days between two dates. Option D subtracts dates directly, which works in Google Sheets but can cause confusion with formatting. Option D tries to convert dates to numbers but is unnecessary here. Option D adds dates, which is incorrect.

❓ Function Choice

intermediate

Choose the correct function to add months to a date

You want to add 3 months to the date in cell A1 (2024-01-31). Which function will correctly return the new date?

A =DATE(A1 + 3)

B =A1 + 90

C =EDATE(A1, 3)

D =MONTH(A1) + 3

💡 Hint

Look for a function that handles month increments properly, even for end-of-month dates.

The EDATE function adds a specified number of months to a date and adjusts for month length. Option C is invalid syntax. Option C adds days, which is not precise for months. Option C returns a number, not a date.

❓ data_analysis

advanced

Identify overdue tasks based on due dates

You have a list of tasks with due dates in column B and completion status in column C ("Done" or "Pending"). You want to count how many tasks are overdue as of today (assume today is 2024-06-01). Which formula correctly counts overdue tasks that are still pending?

A =COUNTIFS(C:C, "Pending", B:B, ">" & TODAY())

B =COUNTIFS(B:B, "<" & TODAY(), C:C, "Pending")

C =SUMPRODUCT((B:B < TODAY()) * (C:C = "Pending"))

D =COUNTIF(B:B, "<" & TODAY())

💡 Hint

Use a function that can apply multiple conditions to count only overdue and pending tasks.

COUNTIFS counts cells meeting multiple criteria: due date before today and status pending. Option B ignores status. Option B works but is more complex. Option B counts future tasks, not overdue.

🎯 Scenario

advanced

Calculate employee tenure in years and months

You have employee start dates in column A. You want to display their tenure as "X years Y months" as of today. Which formula correctly calculates this tenure for the date in A2?

A =DATEDIF(A2, TODAY(), "Y") & " years " & DATEDIF(A2, TODAY(), "YM") & " months"

B =YEAR(TODAY()) - YEAR(A2) & " years " & MONTH(TODAY()) - MONTH(A2) & " months"

C =DATEDIF(A2, TODAY(), "M") & " months"

D =DATEDIF(A2, TODAY(), "Y") & " years"

💡 Hint

Use DATEDIF twice to get full years and remaining months separately.

Option A uses DATEDIF with "Y" for full years and "YM" for leftover months, concatenating them with text. Option A can give wrong months if day is earlier. Options A and D show only months or years, not both.

🧠 Conceptual

expert

Why is date calculation critical in business logic?

Which of the following best explains why date calculations are essential in business logic?

A Because dates help schedule tasks, calculate deadlines, and manage billing cycles accurately.

B Because date calculations are rarely needed and can be ignored in most business cases.

C Because dates are only used for formatting and have no impact on calculations.

D Because dates are stored as text and cannot be used in formulas.

💡 Hint

Think about how businesses rely on time to operate smoothly.

Date calculations allow businesses to track deadlines, schedule events, and manage payments. Ignoring dates or treating them as text causes errors and confusion.