Calculations on various rates

[Iain]

I am putting togethor a worksheet to keep track of costings through the
week. This part is for wages, and staff are paid at 2 rates. Rate 1 (R1) is
from 06:00 to 22:00, and Rate 2 (R2) is from 22:00 to 06:00. I am trying to
automate the hours worked on various shifts so that when I am building a
rosta, it automatically works out the hours worked at the different rates. I
need to keep track of R1 and R2 hours worked on each shift through out the
week (Mon - Sun). ie someone might start work at 21:30 and work through to
07:30, someone else might work 22:30 to 05:30 and someone else 09:00 to
19:00. I have got the general formula, but I cannot get it to work if
someone finishes a night shift before 06:00. C17 is the start time, E17 is
the finish time with F17 listing the total hours worked, G17 listing break to
be deducted and R1 total in H17, R2 total in I17

I would be grateful of any suggestions made. Thanks for your help

 

[Joel]

A formula become extreemly complicated. I would recommend a UDF like the one
below

Function Pay(StartTime, EndTime, R1, R2) As Single
DayShift = 0
NightShift = 0
'time is in days where
'1 day = 1 and 1 hour = 1/24
If EndTime < StartTime Then
EndTime = EndTime + 1 'add 1 day
End If
If StartTime < TimeValue("6:00AM") Then
If EndTime < TimeValue("6:00AM") Then
NightShift = EndTime - StartTime
Else
NightShift = TimeValue("6:00AM") - StartTime
If EndTime <= TimeValue("10:00PM") Then
DayShift = EndTime - TimeValue("6:00AM")
Else
DayShift = TimeValue("10:00PM") - TimeValue("6:00AM")
NightShift = NightShift + (EndTime - TimeValue("10:00PM"))
End If
End If
Else
If EndTime < TimeValue("10:00PM") Then
DayShift = EndTime - StartTime
Else
DayShift = TimeValue("10:00PM") - StartTime
NightShift = EndTime - TimeValue("10:00PM")
End If
End If
Pay = (24 * DayShift * R1) + (24 * NightShift * R2)
End Function

 

[Joel]

I found some cases that were wrong. Here are the corrections

Function Pay(StartTime, EndTime, R1, R2) As Single
Dim DayShift As Single
Dim NightShift As Single

DayShift = 0#
NightShift = 0#

'time is in days where
'1 day = 1 and 1 hour = 1/24
If EndTime < StartTime Then
EndTime = EndTime + 1 'add 1 day
End If
If StartTime < TimeValue("6:00AM") Then
If EndTime < TimeValue("6:00AM") Then
NightShift = EndTime - StartTime
Else
NightShift = TimeValue("6:00AM") - StartTime
If EndTime <= TimeValue("10:00PM") Then
DayShift = EndTime - TimeValue("6:00AM")
Else
DayShift = TimeValue("10:00PM") - TimeValue("6:00AM")
NightShift = NightShift + (EndTime - TimeValue("10:00PM"))
End If
End If
Else
If StartTime < TimeValue("10:00PM") Then
If EndTime < TimeValue("10:00PM") Then
DayShift = EndTime - StartTime
Else
DayShift = TimeValue("10:00PM") - StartTime
If EndTime < (1 + TimeValue("6:00AM")) Then
NightShift = EndTime - TimeValue("10:00PM")
Else
DayShift = DayShift + (EndTime - (1 + TimeValue("6:00AM")))
NightShift = (1 + TimeValue("6:00AM")) - TimeValue("10:00PM")
End If
End If
Else
If EndTime < (1 + TimeValue("6:00AM")) Then
NightShift = EndTime - StartTime
Else
DayShift = (EndTime - (1 + TimeValue("6:00AM")))
NightShift = (1 + TimeValue("6:00AM")) - TimeValue("10:00PM")
End If
End If
End If
Pay = (24 * DayShift * R1) + (24 * NightShift * R2)
End Function