Multiplying hours by different dollar amounts based upon time worked

[cdmacme]

I charge one rate for day work and one for night shifts. My spreadsheet is set to figure the total number of hours worked and I know how to multiply by dollars to get answer #1, but is it possible to use a formula to multiply times a different rate for a night shift?

For example I use =IF(B2<A2,B2+1,B2)-A2 to get the correct number of hours worked for night shifts in C2. Can I then refer to B2 being less than A2 in order to multiply C2 times one dollar amount vs. another? I would like for D2 to automatically calculate the proper dollar total dependent upon the type of shift and number of hours worked.

My specifics are: days = $60/hr, nights (anything that spans midnight) =$90/hr.

Thanks for the help - love the site.

 

[kevinb11]

I tried something like this but received an error. Maybe someone with a little more experience can tweak it if possible.

=if(a2<24:00>b2,c2*90,c2*60)

with a2 = start time
b2 = end time
c2 = hours worked

maybe there is a time formula that would work on this?!?

 

[cdmacme]

This is exactly what I'm going for - yes. I'll try and tweak it myself but would really appreciate the excel pros taking a look too. Thanks for the efforts, very much.

 

[RonB1111]

To determine a rate for multiple conditions, use "OR()" or "AND()". This uses the $90 rate if the time worked crosses midnight or if start time is after midnight but before 6am:

=IF(OR(B2-A2 < 0,A2 < 6/24),C2*90,C2*60) note: remove the spaces around the < and > signs before entering formula to excel

a2 = start time
b2 = end time
c2 = hours worked

B2-A2 tests for crossing midnight
A2 < 6/24 tests for a start time after midnight but before 6:00 am (for before say 8am, change the 6/24 to 8/24)

 

[cdmacme]

Thank you! I only needed to add *24 to the ending parameters so that the final result could be currency. This is exactly what I needed!!

You've helped a ton and saved me hours of manual entering.

THANKS!

 

[RonB1111]

You're welcome. Thank you for the feedback.