Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculations on various rates
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculations on various rates
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 "Iain" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculations on various rates
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 "Joel" wrote: 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 "Iain" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tax Rates | Excel Discussion (Misc queries) | |||
Tax rates | Excel Discussion (Misc queries) | |||
Can't add new rates II | Excel Discussion (Misc queries) | |||
Can't add pay rates | Excel Worksheet Functions | |||
Can't add new rates | Excel Discussion (Misc queries) |