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.

 

[RonB1111]

Yes it can be done, but your post isn't fully displayed. You're example doesn't fully display because you probably used a < or > sign. On this site, you need to place a space before and after a < and > to display completely. I know it looked right when you typed it but when you click post, characters after these signs are often dropped.

 

[cdmacme]

Darn! Didn't notice. Thanks for the heads-up. Will re-post.

 

[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.

 

[RonB1111]

I'm not sure what data elements are in A2 and B2 so I'm not understanding your formula to calculate night shift hours in C2. But if C2 is night shift hours, then here's the formula and you'll have to substitute the correct cell for day shift hours:

=(C2*90)+(?DayShiftHoursCell?*60)

If Day shift hours is not in a cell, you can subtitute with a formula to calculate day shift hours.

 

[cdmacme]

Thanks, Ron. My first cells are beginning time and ending time. Third cell is total hours worked. If the hours are worked during nighttime, the total should be multiplied by 90, otherwise it should be multiplied by 60.

8:00 AM 10:00 AM 2:00 =2*60
22:00 PM 3:00 AM 5:00 =5*90

So I currently have the formula in the third cell to calculate the hours properly even though some are post-midnight. What I'm really eager to do is set up the formula to recognize when the hours were nighttime so that the total can be multiplied by 90 instead of 60.

Have I explained this well? This is my first post - trying my best!

 

[kevinb11]

Thanks, Ron. My first cells are beginning time and ending time. Third cell is total hours worked. If the hours are worked during nighttime, the total should be multiplied by 90, otherwise it should be multiplied by 60.

8:00 AM 10:00 AM 2:00 =2*60
22:00 PM 3:00 AM 5:00 =5*90

So I currently have the formula in the third cell to calculate the hours properly even though some are post-midnight. What I'm really eager to do is set up the formula to recognize when the hours were nighttime so that the total can be multiplied by 90 instead of 60.

Have I explained this well? This is my first post - trying my best!

CD do you want the formula to split the second equation that you posted?
ie.... 2 hours @ $60 and 3 hours @ $90

 

[T. Valko]

Thanks, Ron. My first cells are beginning time and ending time. Third cell is total hours worked. If the hours are worked during nighttime, the total should be multiplied by 90, otherwise it should be multiplied by 60.

8:00 AM 10:00 AM 2:00 =2*60
22:00 PM 3:00 AM 5:00 =5*90

So I currently have the formula in the third cell to calculate the hours properly even though some are post-midnight. What I'm really eager to do is set up the formula to recognize when the hours were nighttime so that the total can be multiplied by 90 instead of 60.

Have I explained this well? This is my first post - trying my best!

You need to be more specific about the night shift.

It starts at midnight but when does night shift end and day shift begin?

 

[RonB1111]

A few more questions about the rules on whether hours are night or day rates:
1. If start time is before midnight and end time is after midnight, are those hours before midnight night or day rate?
2. What time in the morning does the night rate end and day rate begin.
3. If you start work at 4am until noon, which hours are night vs day rate.
4. In your formula to calc C2 night hours, you add 1 hour if it crosses midnight - do you want an extra hour to be paid beyond the reported hours if time crosses midnight?
5. Any other special rules we should consider in developing a formula?

 

[cdmacme]

The shifts vary, some starting and ending before midnight, others spanning midnight. I'm already using a formula to figure the correct number of hours worked (the one above) and then using that to multiply times 60, which is great. The glitch is that night shifts (any that span midnight regardless of start/stop time) should be times 90.

I think that I've made it seem more complicated than it is. On the other hand, I may end up having to incorporate more complication into what is working for the most part now in order to do this, and I'm not sure it's worthwhile.

Thanks very much for the effort -