Excel Formula Capabilities regarding time and pay rates

[emmalouise]

Hi,

I have attached a really basic example as I don't want to create my spreadsheet this way if the formula functions aren't there.

I'll try to explain this best I can.

We have 24hr departments that work 7 days a week, some staff are in separate states and some are home users.

In the attached spreadsheet I have tried to narrow down the variables per user as to whether its a weekend/weekday/public holiday - if they are a home or office user, their start and end time, their hours and their regular rates.

The issue we are having is correctly calculating penalty rates etc as it has been made reasonably complicated.

On the right I've put a penalties table - It would be fine if rates were just based on entire shift times and anything that is done for entire shift or per hour is fine I can easily do that.

Our issue is that overnight rates start at a certain time and finish at a certain time so if your shift runs over any of the time in between you need to have penalty rates added. E.g. 9:00pm - 6:00am would need the overnight rates from 10pm-6am or 3:00am - 10:00am would need them from 3am-6am

Maybe the easiest way would be to create an hours column for the ovnight etc scenarios? so maybe have a regular hours and overnight hours? I am just not too sure how to do this.

Any help is greatly appreciated!

[daddylonglegs]

You can get night hours worked with this formula in I7 copied down

=(G7-F7)*24+(G7<F7)*8-MEDIAN(G7*24,6,22)+MEDIAN(F7*24,6,22)

format as number

[emmalouise]

Thank you so much! This formula is excellent.

Could I please ask one more question about it. If we needed to change the hours for the night rates (just in case) could I confirm what to change in this formula to reference a different time period ?

[daddylonglegs]

6 and 22 in MEDIAN functions are the start and end times, 8 is the overnight hours, so if you wanted to change night hours to 21:30 - 07:00 then formula would need to be like this:

=(G7-F7)*24+(G7<F7)*9.5-MEDIAN(G7*24,7,21.5)+MEDIAN(F7*24,7,21.5)