vendredi 31 août 2018

Excel Date difference without weekend days

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 1st of August, the finish date is Tuesday the 14th 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.

Aucun commentaire:

Enregistrer un commentaire