Hi everyone,
Im trying to create a formula for my overtime, as I get a different pay rate at different days and times.
Mon Fri: 17:00 19:59 = 1
Mon Fri: 20:00 23:59 = 1.5
Mon Sat: 00:00 08:30 = 2
Sat: 08:30 23:59 = 1.5
Sun 00:00 Mon 08:30 = 2
Ive tried using the following formula, but get a False output.
I have no idea how to work out the days etc.
=IF(AND(C8>=TIMEVALUE(17:00))*(C8<=TIMEVALUE(19:59″)),1,IF(AND(C8>=TIMEVALUE(20:00))*(C8<=TIMEVALUE(23:59″)),1.5,IF(AND(C8>=TIMEVALUE(00:00))*(C8<=TIMEVALUE(08:29″)),2)),IF(AND(C8>=TIMEVALUE(08:30))*(C8<=TIMEVALUE(17:00″)),1.5)))
Thank you for your help!
J
Bookmarks