I have too many levels of nesting. I am sure there is a better way to do this any help would be appreciated.
=IF(N3="URS",IF(L3<6,"100",IF(L3<12,"80",IF(J3=1,IF((L3-15)<6,"100",IF((L3-15)<12,"80",IF(J3=2,IF((L3-30)<12,"80",IF((L3-30)<24,"50",IF(J3=3,IF((L3-45)<24,"50","FAIL")))))))))))
Basically if URS jobs are completed in less than 6 hours they get "100" less than 12 "80" less than 24 "50" more than 24 "0" however it is accross working days (8am - 5pm)
Break down:
If it is a URS job
If it is completed in less than 6 hours get 100
If it is completed in less than 12 hours get 80
If it is 1 calander day
If (total hours - 15) is less than 6 get 100
If (total hours - 15) is less than 12 get 80
If it is 2 calander days
If (total hours - 30) is less than 12 get 80
If (total hours - 30) is less than 24 get 50
If it is 3 calander days
If (total hours - 45) is less than 24 get 50
must be more than 24 hours get FAIL
Bookmarks