Excel Date difference without weekend days

Question

Last time I posted a quite vague story about a date difference challenge which I haven't solved yet. I will try to elaborate since I have tried everything in my power and the problem still isn't fixed.

I currently have three columns.

• Column 1 (F)
  • the date a car starts its repairs (format DayOfWeek-DD-MM-YYYY)
• Column 2 (G)
  • the number of days in which the car is repaired (service level agreement [SLA]; the standard is 10 days)
• Column 3 (H)
  • the output, which is the date the car should be finished. So the number of days after the startdate*

^{*Th thing which makes this case difficult is that only weekdays are included.}

So, for example:

If a car starts repairs on Monday 1^st of August, the finish date is Tuesday the 14^th of August.

I tried to solve this with the following formula:

=IF(WEEKDAY(F218)=2;(F218+11);
  IF(WEEKDAY(F218)=3;F218+12;
  IF(WEEKDAY(F218)=4;F218+13;
  IF(WEEKDAY(F218)=5;F218+14;
  IF(WEEKDAY(F218)=6;F218+15)))))

In other words:
If startdate = Monday then startdate + 11,
if startdate = Tuesday then startdate + 12, etc.

This works, but I have 300+ rows and dragging this function down doesn't change the cell references.

I know about the NETWORKDAYS and WEEKDAY functions, but I encounter problems with any Monday where only 1 weekend passes and other days where 2 weekends pass.

Answer 1

First of all, I am assuming that your first day - whatever day that may be - is considered day one (1). So in my scenario, if a SLA states 2 days to complete a repair and the start date is a Monday, I'm assuming the repair should be completed by Tuesday.

My assumption is based off this comment by @RonRosenfeld:

...although you might have to subtract 1 from the number of days

---

With all that being said, try this formula in your cell instead:
 ^{NOTE: You may need to change things like commas and semi-colons to adjust for your region.}

=WORKDAY($F2,$G2-1)+LOOKUP(WEEKDAY(WORKDAY($F2,$G2-1),16),{1;2;3},{2;1;0})

What it does:

• WORKDAY($F2,$G2-1)
  • First we want to find out exactly what day the repairs should be completed by if weekend days (Saturday and Sunday) were included. This part of the formula will simply give us a place to start.
  • $F2 is your repair start date
  • $G2 is the number of days a repair is supposed to take (you may need to add a column for this, because, as you stated, the SLA may change and you need the formula to be easily adjusted)
    
• WEEKDAY(WORKDAY($F2,$G2-1),16)
  • The WORKDAY function from above is wrapped inside a WEEKDAY function. This WEEKDAY function is written to account for each day of a week to be assigned numbers. The [return_type] parameter of 16 tells Excel to label them as "Numbers 1 (Saturday) through 7 (Friday)". We chose 16 so that our LOOKUP function is easier to write. This part of the formula only returns a one-digit number, which in turn will be used to figure out what day of the week we actually want when excluding weekends.
• LOOKUP(WEEKDAY(WORKDAY($F2,$G2-1),16),{1;2;3},{2;1;0})
  • We finish the formula by adding the result from a LOOKUP function using the first form of the function:
    LOOKUP(lookup_value,lookup_vector,[result_vector])
  • We found our lookup_value in the previous bullet point using the WEEKDAY function. Now we want Excel to use the lookup_vector - {1;2;3} in our formula - to find the correct value to add to the first part of our formula (which is found using the [result_vector] - {2;1;0} in our formula).
  • The lookup_vector only has three values: 1, 2, and 3.
  • 1 signals Saturday
  • 2 signals Sunday
  • 3 signals all other days
• Think of the lookup_vector and [result_vector] as forming a matrix/table from which our value is found:
  • 1   2
  • 2   1
  • 3   0
• If our number of repair days pushes us to:
  • a Saturday (1), the formula adds 2.
  • a Sunday (2), the formula adds 1.
  • any weekday, the formula adds 0 (since weekdays are acceptable).

---

Hopefully all of this makes sense. Best of luck to you!