Time Calculations over a week

[Sara]

Hi there,

I have a spreadsheet to calculate shift hours with each day of the week
stating start and finish times.
A1 = Sun Start, B1 = Sun Finish, C1 = Mon Start, D1 = Mon Finish etc
I'm trying to calculate the total number of nightrate hours for a week (to 2
decimals points) worked between 8pm & 6am.
This formula also needs to subtract the lunchbreak of 1/2 hour after 5 hours
work.
Shifts range from 4 hours to 8.5 hours per day and not all shifts go into
the nightrate period.

Any ideas?

 

[ShaneDevenshire]

Hi,

Suppose they work 5.25 hours what do you give them for a lunch break 1/2
hour, 1/4 hour or something else? If they work an 8.5 hour shift do you
count 8.5 hours of work and 1/2 hour lunch or 8 hours work and 1/2 hour work?
If they complete an 8 hour shift at 6PM but haven't taken lunch do you
consider the lunch (6:00-6:30 PM) as night shift?

 

[Sara]

Lunch breaks are always 1/2 hour long.
A shift of 5.25 hours doesn't exist - though I have seen a 5.5 hour shift.
An 8.5 hour shift is 8 hours work, 1/2 hour lunch.
The break at 5 hours is where the rule book states the lunch must be
calculated from for pay purposes, however practise generally puts it where
ever they want.

Nightrate is only ever valid for hours worked between 8pm and 6am.
So if a person works from 1700 - 01:30, I need the calc to show 5 hours
nightrate
17:00 - 20:00 normal rate (3 hours)
2000 - 22:00 nightrate (2 hours)
22:00 - 22:30 lunch unpaid
2230 - 0130 nightrate (3 hours)

Am I asking excel to do too much?

 

[ShaneDevenshire]

No your not asking it to do too much, but experience tells me that these
benefits calculation can turn out to be a lot more complicate than initially
stated. I have constructed timesheet/pay worksheets that would make your
hair curl because there were so many variations to deal with. For those
types of problem spreadsheet formulas were just not possible and I had to
write VBA custom functions for everthing.

Well I look over all of your responses and see what I can come up with.

 

[Sara]

How about simplifying things?

What would be the formula if we don't take the lunch break into consideration?
I can then adapt the roster's to break for lunch

 

[ShaneDevenshire]

Hi,

If you are wondering why it took so long:

There are 8 conditions
Start<6AM Finish<6AM
Start<6AM Finish>=6AM
Start>6AM Finish<8PM
Start<=8PM Finish>8PM
Start>8PM Finish>8PM
Start<=8PM Finish Next day
Start>8PM Finish Next day before 6 am
Start>8PM Finish Next day after 6 AM

So the formula is:
=IF(AND(A2<6/24,B2<=6/24),B2-A2,IF(AND(A2<=6/24,B2>=6/24),6/24-A2,IF(AND(A2<=20/24,B2>=20/24,B2<=24/24),B2-20/24,IF(AND(A2>=20/24,B2<=1,B2>20/24),B2-A2,IF(AND(A2<=20/24,B2<=6/24),4/24+B2,IF(AND(A2>=20/24,B2<=6/24),1-A2+B2,IF(AND(A2>=20/24,B2>=6/24),1-A2+6/24,0)))))))

And this doesn't take lunch breaks into account.

The start times are in A2, the end times in B2. It may be that you define a
shift not to cross the day line, in which case we could make the formula
simplier.

If this helps, please click the Yes button.