If statement between times and days

Hi everyone,

I’m 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

I’ve tried using the following formula, but get a “False” output.

=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

By: James

Leave a Reply

Your email address will not be published. Required fields are marked *