PHIL.Pearce84
Board Regular
- Joined
- May 16, 2011
- Messages
- 152
- Office Version
- 365
- Platform
- Windows
HI I have the following formula:
=IFERROR(IF(O2="Sun","Overtime X2",IF(O2="Sat",LOOKUP(U2,{0,"08:00","12:01"}+0,{#N/A,"Overtime X1.5","Overtime X2"}),LOOKUP(U2,{0,"17:31","23:59"}+0,{"Overtime X2",#N/A,"Overtime X1.5"}))),M2)
This formula works great for the "Sat" and "Sun" criteria, it doesn't seem to pick up the overtime on the weekdays though, maybe it would help if I explained the context.
Where it's not a sat/sun and instead Mon-Fri it should show as "Overtime X1.5" between the hours of 17:31-23:59 and then "Overtime X2" between the hours 00:00-06:00.
Anything that doesn't meet this criteria should return the value in M2
=IFERROR(IF(O2="Sun","Overtime X2",IF(O2="Sat",LOOKUP(U2,{0,"08:00","12:01"}+0,{#N/A,"Overtime X1.5","Overtime X2"}),LOOKUP(U2,{0,"17:31","23:59"}+0,{"Overtime X2",#N/A,"Overtime X1.5"}))),M2)
This formula works great for the "Sat" and "Sun" criteria, it doesn't seem to pick up the overtime on the weekdays though, maybe it would help if I explained the context.
Where it's not a sat/sun and instead Mon-Fri it should show as "Overtime X1.5" between the hours of 17:31-23:59 and then "Overtime X2" between the hours 00:00-06:00.
Anything that doesn't meet this criteria should return the value in M2
