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