WEEKDAY and WORKDAY in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to get the weekday number of the date in cell A1.

Excel

=WEEKDAY([1])

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

AA1

B1

C"A1"

DDATE(2023,1,1)

Attempts:

3 left

💡 Hint

Common Mistakes

Using a number like 1 instead of a cell reference.

Putting the cell reference in quotes like "A1" which makes it text.

The WEEKDAY function needs a date value or cell reference containing a date. Using A1 correctly references the date in cell A1.

2fill in blank

medium

Complete the formula to find the date 5 workdays after the date in cell B2.

Excel

=WORKDAY([1], 5)

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

AB2

B5

C"B2"

DTODAY()

Attempts:

3 left

💡 Hint

Common Mistakes

Using a number like 5 as the start date.

Putting the cell reference in quotes like "B2".

The WORKDAY function needs a start date as the first argument. Using B2 correctly references the start date.

3fill in blank

hard

Fix the error in the formula to get the weekday number of the date in C3 with Monday as day 1.

Excel

=WEEKDAY(C3, [1])

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

A1

B2

C3

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Using 1 which counts Sunday as 1.

Using 0 or 3 which are invalid or count days differently.

Using 2 as the second argument makes WEEKDAY count Monday as 1, Tuesday as 2, and so on.

4fill in blank

hard

Fill both blanks to create a formula that finds the date 10 workdays after D4, excluding holidays listed in F1:F5.

Excel

=WORKDAY([1], [2], F1:F5)

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

AD4

B10

C5

DTODAY()

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the start date and number of days.

Using a number instead of a cell reference for the start date.

The first argument is the start date D4. The second argument is the number of workdays to add, which is 10.

5fill in blank

hard

Fill all three blanks to create a dictionary comprehension that maps each date in the list 'dates' to its weekday number starting on Monday.

Excel

{ [1]: WEEKDAY([2], [3]) for [2] in dates }

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

Adate

Bd

C2

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Using the same variable name for key and loop variable.

Using 1 instead of 2 for the WEEKDAY type argument.

The key is each date, so date. The variable iterating over dates is d. The WEEKDAY second argument 2 makes Monday day 1.

Practice

1. What does the Excel function =WEEKDAY(A1) return when A1 contains a date?

easy

A. The date of the next workday after the date in A1

B. A number representing the day of the week for the date in A1

C. The total number of workdays in the month of A1

D. The name of the day (like Monday) for the date in A1

Solution

1. Step 1: Understand WEEKDAY function purpose

   The WEEKDAY function returns a number from 1 to 7 representing the day of the week for a given date.

2. Step 2: Apply to cell A1

   Since A1 contains a date, =WEEKDAY(A1) returns the day number of that date.

3. Final Answer:

   A number representing the day of the week for the date in A1 -> Option B

4. Quick Check:

   WEEKDAY = day number [OK]

Hint: WEEKDAY gives day number, not name or date [OK]

Common Mistakes:

• Thinking WEEKDAY returns the day name
• Confusing WEEKDAY with WORKDAY
• Expecting a date instead of a number

2. Which of these is the correct syntax to find the next workday after the date in cell B2, excluding weekends?

easy

A. =WORKDAY(B2, 1)

B. =WORKDAY(B2 + 1)

C. =WEEKDAY(B2, 1)

D. =WORKDAY(B2, "next")

Solution

1. Step 1: Recall WORKDAY syntax

   WORKDAY(start_date, days) returns the date after adding 'days' workdays to start_date, skipping weekends.

2. Step 2: Apply to find next workday

   To find the next workday after B2, add 1 workday: =WORKDAY(B2, 1) is correct syntax.

3. Final Answer:

   =WORKDAY(B2, 1) -> Option A

4. Quick Check:

   WORKDAY(date, 1) = next workday [OK]

Hint: WORKDAY needs two arguments: date and days [OK]

Common Mistakes:

• Using WORKDAY with only one argument
• Confusing WEEKDAY with WORKDAY
• Passing text like "next" instead of a number

3. If cell C3 contains the date 2024-06-14 (a Friday), what will =WORKDAY(C3, 3) return?

medium

A. 2024-06-17 (Monday)

B. 2024-06-18 (Tuesday)

C. 2024-06-20 (Thursday)

D. 2024-06-19 (Wednesday)

Solution

1. Step 1: Identify start date and days to add

   Start date is 2024-06-14 (Friday). We add 3 workdays, skipping weekends.

2. Step 2: Count workdays skipping weekend

   Adding 1 workday: Monday 2024-06-17
   Adding 2 workdays: Tuesday 2024-06-18
   Adding 3 workdays: Wednesday 2024-06-19

3. Final Answer:

   2024-06-19 (Wednesday) -> Option D

4. Quick Check:

   WORKDAY adds days skipping weekends [OK]

Hint: Count only weekdays, skip Sat & Sun [OK]

Common Mistakes:

• Counting weekend days as workdays
• Adding days directly without WORKDAY
• Confusing date formats

4. The formula =WORKDAY(A1, 5, {"2024-06-19"}) returns an error. What is the likely cause?

medium

A. WORKDAY does not accept a third argument

B. The days argument cannot be a positive number

C. The holidays argument must be a range, not an array of text strings

D. The date in A1 is invalid

Solution

1. Step 1: Understand WORKDAY holiday argument

   The third argument for WORKDAY is a range or array of dates, not text strings in quotes.

2. Step 2: Identify error cause

   Using {"2024-06-19"} passes a text string, not a date value, causing an error.

3. Final Answer:

   The holidays argument must be a range, not an array of text strings -> Option C

4. Quick Check:

   Holidays must be date values, not text [OK]

Hint: Holidays must be date cells or date arrays, not text [OK]

Common Mistakes:

• Passing holiday dates as text strings
• Ignoring the third argument format
• Assuming WORKDAY only takes two arguments

5. You want to find the weekday number of the date that is 10 workdays after 2024-06-01, excluding weekends and the holiday 2024-06-12. Which formula correctly does this?

hard

A. =WEEKDAY(WORKDAY("2024-06-01", 10, DATE(2024,6,12)))

B. =WORKDAY(WEEKDAY("2024-06-01"), 10, DATE(2024,6,12))

C. =WEEKDAY(WORKDAY("2024-06-01", 10, {"2024-06-12"}))

D. =WORKDAY("2024-06-01", 10, "2024-06-12")

Solution

1. Step 1: Calculate date 10 workdays after 2024-06-01 excluding holiday

   Use WORKDAY with start date, days, and holiday as DATE(2024,6,12) to get correct date.

2. Step 2: Find weekday number of that date

   Wrap WORKDAY result inside WEEKDAY to get the day number.

3. Final Answer:

   =WEEKDAY(WORKDAY("2024-06-01", 10, DATE(2024,6,12))) -> Option A

4. Quick Check:

   WORKDAY then WEEKDAY for weekday number [OK]

Hint: Use WORKDAY first, then WEEKDAY to get day number [OK]

Common Mistakes:

• Passing holiday as text instead of DATE function
• Using WEEKDAY inside WORKDAY incorrectly
• Ignoring holidays in WORKDAY