I am hoping someone can please help me! How would I write the formula to do this? If Route # = 002 and Route Time = late and Days <> M-F, vlookup W-TH on another tab and subtract that value from 15 and enter it in the PM row.
In the end I want rows 1 and 2 to say 15 in the number of days column. I want rows 3 and 4 to say 9 in the number of days column and row 5 should say 6. These numbers are because there are 15 billable days in December. I have tried the two following versions of formulas and neither returned the correct result. If anyone can tell me how to fix these or suggest another way I would be extremely appreciative!!
=IF(AND(A2:A918=A6,C2:C918<>"M-F"),(15-VLOOKUP(and(A2:A918=A6,C2:C918<>"M-F"),'LR Days'!A1:B17,2,FALSE)),VLOOKUP(C6,'LR Days'!A1:B17,2,FALSE))
=IF(COUNTIFS(A6:A921,A6,B6:B921,"LATE",C6:C921,"<>M-F"),(VLOOKUP("M-F",'LR Days'!$A$1:$B$17,2,FALSE)-VLOOKUP(Summary!C6,'LR Days'!$A$1:$B$17,2,FALSE)),VLOOKUP(C6,'LR Days'!$A$1:$B$17,2,FALSE))
Route # Route Time Days Start Time End Time Number of Days
001 AM M-F 6:30 8:45 15
001 PM M-F 14:50 16:30 15
002 AM M-F 6:50 8:45 9
002 PM M-F 3:50 16:37 9
002 LATE W-TH 17:00 18:05 6
Bookmarks