Why date handling is common in business in Excel - Challenge Your Understanding

Challenge - 5 Problems

🎯 Scenario

intermediate

Calculating Days Between Two Dates

You have a spreadsheet with start dates in column A and end dates in column B. You want to calculate the number of days between these dates in column C. Which formula in cell C2 will correctly calculate the days difference?

A =DATEDIF(A2, B2, "m")

B =A2-B2

C =B2-A2

D =DATEDIF(B2, A2, "d")

💡 Hint

Subtract the earlier date from the later date to get the number of days.

Subtracting the start date from the end date gives the number of days between them. Option C does this correctly. Option C reverses the order and may give negative results. Option C uses DATEDIF but reverses the dates, causing errors. Option C calculates months, not days.

📊 Formula Result

intermediate

Result of Adding Days to a Date

If cell A1 contains the date 2024-06-01, what will be the result of the formula =A1+15?

A 2024-06-16

B 2024-06-15

C 15

D Error

💡 Hint

Adding a number to a date adds that many days.

In Excel, dates are stored as numbers. Adding 15 to a date adds 15 days. So 2024-06-01 plus 15 days is 2024-06-16.

❓ Function Choice

advanced

Choosing the Right Function to Extract Month

You want to extract the month number from a date in cell A1. Which formula will correctly do this?

A =DATE(A1)

B =MONTH(A1)

C =DAY(A1)

D =YEAR(A1)

💡 Hint

The MONTH function returns the month number from a date.

=MONTH(A1) returns the month number from the date in A1. DATE is for creating dates, DAY returns the day number, and YEAR returns the year.

❓ data_analysis

advanced

Finding the Number of Weekdays Between Dates

You want to calculate the number of weekdays (Monday to Friday) between two dates in cells A2 (start) and B2 (end). Which formula will give the correct count?

A =NETWORKDAYS(A2, B2)

B =B2-A2

C =WORKDAY(A2, B2)

D =DATEDIF(A2, B2, "w")

💡 Hint

Use a function that counts only weekdays between two dates.

NETWORKDAYS counts weekdays between two dates. Option A counts all days. WORKDAY calculates a future date after a number of workdays, not the count. DATEDIF with "w" counts complete weeks, not weekdays.

🧠 Conceptual

expert

Why Date Handling is Crucial in Business

Why is handling dates correctly important in business spreadsheets?

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

B Because dates are only used for decoration and do not affect calculations.

C Because dates automatically fix errors in other data.

D Because dates help track deadlines, payments, and schedules accurately.

💡 Hint

Think about what businesses need to manage daily.

Dates are essential for tracking deadlines, payments, schedules, and reporting. Proper date handling ensures accurate calculations and planning. Other options are incorrect because dates are functional, not decorative, do not fix errors automatically, and are stored as numbers, not just text.