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

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to get the current date in Excel.

Excel

=TODAY([1])

Drag options to blanks, or click blank then click option'

ANOW()

B()

C[]

D{}

Attempts:

3 left

💡 Hint

Common Mistakes

Leaving out the parentheses

Using square or curly brackets instead of parentheses

The TODAY function in Excel does not take any arguments, so it must be called with empty parentheses ().

2fill in blank

medium

Complete the formula to calculate the number of days between two dates in cells A1 and B1.

Excel

=B1 [1] A1

Drag options to blanks, or click blank then click option'

A+

B-

C*

D/

Attempts:

3 left

💡 Hint

Common Mistakes

Adding the dates instead of subtracting

Multiplying or dividing dates

Subtracting the earlier date (A1) from the later date (B1) gives the number of days between them.

3fill in blank

hard

Fix the error in the formula to add 7 days to the date in cell A1.

Excel

=A1 [1] 7

Drag options to blanks, or click blank then click option'

A*

B+

C-

D/

Attempts:

3 left

💡 Hint

Common Mistakes

Using multiplication or division instead of addition

Using subtraction instead of addition

To add days to a date, you use the plus sign + in Excel.

4fill in blank

hard

Fill both blanks to calculate the number of full months between dates in A1 and B1 using the DATEDIF function.

Excel

=DATEDIF(A1, B1, [1] [2])

Drag options to blanks, or click blank then click option'

A"M"

B"D"

C"Y"

D"MD"

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong units like "D" or "Y" for months

Not putting the unit in quotes

The DATEDIF function requires the start date, end date, and a unit in quotes. To get full months, use "M" as the unit. The second blank is a distractor and should be empty or ignored.

5fill in blank

hard

Fill all three blanks to create a formula that extracts the year, month, and day from the date in cell A1.

Excel

=YEAR([1]) & "-" & MONTH([2]) & "-" & DAY([3])

Drag options to blanks, or click blank then click option'

AA1

BB1

CC1

DTODAY()

Attempts:

3 left

💡 Hint

Common Mistakes

Using different cells for year, month, and day

Using TODAY() instead of the date cell

The YEAR, MONTH, and DAY functions each take a date cell as input. Using A1 for all extracts the parts from the same date.

Practice

1. Why is date handling important in business spreadsheets?

easy

A. To change font colors automatically

B. To track deadlines and schedules easily

C. To create charts from text data

D. To sort data alphabetically

Solution

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

   Dates help organize tasks and deadlines, which are crucial for business planning.

2. Step 2: Identify the correct purpose of date handling

   Date handling is mainly used to track time-related events, not for formatting or sorting text.

3. Final Answer:

   To track deadlines and schedules easily -> Option B

4. Quick Check:

   Date handling = Track deadlines [OK]

Hint: Dates help manage time and deadlines in business [OK]

Common Mistakes:

• Confusing date handling with text formatting
• Thinking dates are only for sorting alphabetically

2. Which of these is the correct way to enter a date in Excel?

easy

A. 15/06/2024

B. June 15, 2024

C. All of the above

D. 2024-06-15

Solution

1. Step 1: Recognize Excel date formats

   Excel accepts dates in multiple formats like ISO (YYYY-MM-DD), written month-day-year, and day/month/year depending on settings.

2. Step 2: Confirm all options are valid date entries

   All given formats are valid ways to enter dates in Excel cells.

3. Final Answer:

   All of the above -> Option C

4. Quick Check:

   Multiple date formats accepted = All of the above [OK]

Hint: Excel accepts many date formats; try your local style [OK]

Common Mistakes:

• Assuming only one date format works
• Entering dates as plain text without separators

3. Given A1 contains 2024-06-01 and B1 contains 2024-06-10, what does the formula =B1-A1 return?

medium

A. 1

B. 10

C. Error

D. 9

Solution

1. Step 1: Understand date subtraction in Excel

   Excel stores dates as numbers; subtracting two dates gives the number of days between them.

2. Step 2: Calculate days between June 1 and June 10

   June 10 minus June 1 equals 9 days difference.

3. Final Answer:

   9 -> Option D

4. Quick Check:

   10 - 1 = 9 days difference [OK]

Hint: Subtract dates to get days between [OK]

Common Mistakes:

• Counting both start and end day (off by one)
• Expecting date format instead of number

4. You want to calculate the number of days between two dates in A1 and B1. Which formula has an error?

medium

A. =B1+A1

B. =DATEDIF(A1,B1,"d")

C. =B1-A1

D. =NETWORKDAYS(A1,B1)

Solution

1. Step 1: Review formulas for date difference

   Subtracting dates, DATEDIF, and NETWORKDAYS correctly calculate days between dates.

2. Step 2: Identify incorrect formula

   Adding two dates (=B1+A1) does not calculate days difference and gives a meaningless number.

3. Final Answer:

   =B1+A1 -> Option A

4. Quick Check:

   Add dates ≠ days difference [OK]

Hint: Subtract or use DATEDIF, don't add dates [OK]

Common Mistakes:

• Using addition instead of subtraction
• Confusing NETWORKDAYS with simple subtraction

5. A project starts on 2024-06-01 in A1. You want to find the deadline 15 working days later, excluding weekends. Which formula should you use?

hard

A. =WORKDAY(A1,15)

B. =DATEDIF(A1,15,"d")

C. =A1+NETWORKDAYS(A1,15)

D. =A1+15

Solution

1. Step 1: Understand working days calculation

   WORKDAY function adds working days excluding weekends to a start date.

2. Step 2: Choose formula that adds 15 working days

   =WORKDAY(A1,15) correctly calculates the date 15 business days after the start date.

3. Final Answer:

   =WORKDAY(A1,15) -> Option A

4. Quick Check:

   Use WORKDAY to add business days [OK]

Hint: Use WORKDAY to add business days, not simple addition [OK]

Common Mistakes:

• Adding days without excluding weekends
• Using NETWORKDAYS incorrectly as addition