Formula for deciding shift differential based on a start time

[Jasonpen]

Hi i'm trying to figure out how to write a formula that would look at a shift start time and based on that time would output the correct shift premium to the cell. So I have 3 scenarios a start time from 05:00 to 13:59 no premium. 14:00 to 21:59 .75 cents per hour and lastly 22:00 to 04:59 $1.00 per hour. I've tried using an if and statement and I can make it work for one of the start times but am unsure of the syntax to get it to analyze everything or if that is just not the correct way to do what i'm looking to accomplish. Any help would be appreciated thanks.

 

[RasGhul]

Hi Jason,

If you can post some sample data we can check it out.

Ras

 

[Jasonpen]

=IF(AND(H3>=TIME(14,0,0),H3<=TIME(21,59,0)),"$0.75","$0.00")
This is the formula I used but I don't know how to do more comparisons. The data is just entering the start time in one cell in 24:00 time format. Just need a way for it to know what time slot the start time fell into so it can assign the correct shift premium.

 

[RasGhul]

Try,

=IF(AND(H1>=TIME(14,0,0),H1<=TIME(21,59,0)),0.75,IF(AND(H1>=TIME(22,0,0),H1<=1.207638889),1,0))

 

[Jasonpen]

Thanks for your help. That formula didn't work quite right but I found some other info and I got one that works exactly like I want. =IF(AND(H3>=TIME(5,0,0),H3<=TIME(13,59,0)),"$0.00",(IF(AND(H3>=TIME(14,0,0),H3<=TIME(21,59,0)),"$0.75","$1.00")))

 

[RasGhul]

Ok thanks for the feedback