# Excel Formula to Calculate Premium or Night Hours worked on any Shift.

1. ## Excel Formula to Calculate Premium or Night Hours worked on any Shift.

Hi Forum,
I am trying to develop a universal excel formula in Office 365 to calculate the number of Late Hours or Night Hours worked on any shift.
The universal formula should be able to do the calculation for any Shift start and end times and for any Late premium and Night premium intervals.
In the example below, I will use the formula to calculate "Hrs?" in decimal format (e.g. 7.5 Hours).
Can you help?
Fran99, Dublin.

Week Mon Tue Wed

Shift Start 1 07:00 21:00 15:00
End 17:00 07:00 01:00

Hours Worked 10 10 10

Late 18:00 Hrs? Hrs? Hrs?
20:00

Night 20:00 Hrs? Hrs? Hrs?
08:00

2. ## Re: Excel Formula to Calculate Premium or Night Hours worked on any Shift.

Can you please load a sample workbook of what you expect the result to be? I'll see if i can add the formulae for you as i use a sheet where i work that calculates hours, OT hours and Unsociable hours for me.
Regards

Rich

3. ## Re: Excel Formula to Calculate Premium or Night Hours worked on any Shift.

Hi Richie & Peter,
Essentially I am looking for a formula to take 4 variable.
> Shift start time. S1 e.g. 18.00
> Shift finish time. S2 e.g. 08.00 the following morning.
> Premium period start time. P1. e.g. 20.00
> Premium period end time. P2. e.g. 08.00 the following morning.
Example premium periods are twilight 18.00 to 20.00 and night premium 20.00 to 08.00 the next morning.
I am seeking a formula that can handle any shift start time, any shift finish time, any premium period start time and and end time.
The formula needs to calculate how many premium hours are worked in that shift.
There is no date information in the data.
This formula will be used to evaluate the premiums attaching to a roster, not to calculate actual day to day premiums and pay.
I hope this explaination makes sense.
Regards,
Frank

4. ## Re: Excel Formula to Calculate Premium or Night Hours worked on any Shift.

One generic method is
Formula:

This formula covers the twilight period that you describe in post #4, you would need a formula like this for each period if you want to calculate them individually.

Note that this will give the result in time format, if you want decimal format then you need to multiply the result by 24.

There are currently 1 users browsing this thread. (0 members and 1 guests)