Calculate daily pay

[shell2133]

Hi everyone. I want to be able to calculate the daily pay for agency staff but their pay rate depends on the day of the week and what time they work, whether it's day or night rate. Sample spreadsheet here with the shifts worked on the first tab and all the agency information on the info tab: Agency pay - hopefully this works and has all the info needed.

Any help you can give would be much appreciated

 

[Anfinsen]

WIth all the different pay rates, for example, A Jones works Saturday night, 8 PM to Sunday Morning, 8 AM. is that considered Sat night rate for the entire shift?
What are the hour ranges for Day rate versus night range? Need to know those qualifiers. also, is the holiday rate in addition to regular rates, or does it supplant the regular rate?

 

[shell2133]

A Jones - would be Sat night rate from 8pm Sat to 06.59am Sunday morning, then 7am-8am Sunday would be at Sunday day rate. The hour ranges are at the bottom of the info sheet as follows:
Shift start time Shift End Time
Day rate 07:00 19:59
Night rate 20:00 06:59

The bank holiday rate supplants the regular rate. If I'm making life too complicated with the bank holiday rates I don't mind ignoring that and just being able to work it out based on the day of the week and time. As I don't know how to do it I don't know how complicated it is. Thank you for your help

 

[Anfinsen]

A Jones - would be Sat night rate from 8pm Sat to 06.59am Sunday morning, then 7am-8am Sunday would be at Sunday day rate. The hour ranges are at the bottom of the info sheet as follows:
Shift start time Shift End Time
Day rate 07:00 19:59
Night rate 20:00 06:59

The bank holiday rate supplants the regular rate. If I'm making life too complicated with the bank holiday rates I don't mind ignoring that and just being able to work it out based on the day of the week and time. As I don't know how to do it I don't know how complicated it is. Thank you for your help

got it, are the breaks paid as well?

 

[shell2133]

No, the breaks are deducted.

 

[Anfinsen]

This is a toughie. I spent a little time on it myself, and got stuck. I did find this resource below, which seems to be what you are looking for, check it out.

TIME REGISTRATION | Calculate multiple time shift hours

You can use VBA for time registration. Building a procedure that calculates the number of hours a person worked is not difficult. How does it work?

www.excelacademy.nl

 

[shell2133]

Thank you for trying, I will have a look

 

[Herakles]

I've been thinking about how this can be simplified which I am sure that it can.

Split the three different sets of data on the 'Info' tab into three worksheets:

AgencyRatesPaid
BankHolidays
ShiftTimes

Restructure the AgencyPayRates grid. The data can be edited as it is at the moment but restructuring it for purposes
of calculating costs would be a good idea. This would enable simple standard Excel formulas to be used to find out the
cost of each shift. See image. VBA code can be used to carry out this restructuring process when the pay rates change.

Can it be assumed that:
If someone starts a night shift early (before 20:00) that the hours worked be recorded in a seperate row.
If someone finishes a night shift late (after 07:00) that the hours worked be recorded in a seperate row.
If someone starts a day shift early (before 07:00) that the hours worked be recorded in a seperate row.
If someone finishes a day shift late (after 20:00) that the hours worked be recorded in a seperate row.
That those on nightshift all have their break after midnight.
That the rate for bank holidays be stored as a percentage uplift and that this be applied to the standard M-F rate.

To be able to calculate the cost of each shift worked the following needs to be ascertained:
Day time hours minus breaktime if applicable.
Night time hours before midnight minus breaktime if applicable.
Night time hours after midnight minus breaktime if applicable.

The 'cost' in the 'Shifts worked' tab can be calculated using a cell formula but needs to be stored as the actual value as new
pay rates will need to be used when rates increase over time.

Pay rates do increase over time but using shift rates for different time periods at the same time should be avoided. To be able to do this all shifts
for one pay rate period need to be recorded and costs calculated before the new pay rates are applied. Having said this,
there are ways to be using using pay rates from more than one pay rate period at the same time.

 

[shell2133]

Thank you for looking at this The shifts wouldn't be able to be recorded on separate rows I don't think, as the actual data I use is thousands of lines so would take ages to split them all. I can assume the breaks are all taken after midnight. The bank holiday could be stored as a percentage, I think I am making it too complicated anyway by trying to include these, and for the sake of 8 days out of the year, it might be easier to just disregard the bank holidays and treat them as normal days/nights. The agency spreadsheet will be renewed each month so I will be saving the costs as values before updated for a new months data, so a change in pay rate would be ok and not affect existing lines.

 

[Herakles]

I have added three columns to split the calculations although these could be combined. Splitting them is useful for checking scenaro's.

Column L - FirstDayHours
=IF(INT(F2)=INT(D2),(G2-E2)*24,0)

Column M - HoursBeforeMidnight
=IF(L2=0,(F2-(D2+E2))*24,0)

Column N - HoursAfterMidnight
=IF(L2=0,(F2+G2)-F2,0)*24

Also this code will restructure the pay rates grid in the 'Info' worksheet and create a worksheet called 'AgencyRatesPaid'.

Make sure that there is a blank line before the Bank Holiday dates.

Are you OK to do the formulas in the Cost column?

Public Sub subRestructurePayRatesGrid()
Dim rngData As Range
Dim rngColumn As Range
Dim rngRow As Range
Dim s As String
Dim WsInfo As Worksheet
Dim WsPayRates As Worksheet
Dim intRows As Integer
Dim Q As String
Dim strFormula As String
Dim arrHeaders() As String

    ActiveWorkbook.Save
    
    Set WsInfo = Worksheets("Info")
    If WsInfo.Range("A1").Value = "Agency Rates Paid" Then
        WsInfo.Rows(1).Delete
    End If
                
    Set rngData = WsInfo.Range("A1").CurrentRegion
    
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("AgencyRatesPaid").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    Worksheets.Add after:=Sheets(Sheets.Count)
    ActiveSheet.Name = "AgencyRatesPaid"
    
    Set WsPayRates = Worksheets("AgencyRatesPaid")
    
    WsPayRates.Activate
        
    arrHeaders = Split("Agency Name,Employee Grade,Day,Weekday From,Weekday To,Night or Day,Rate,", ",")
    WsPayRates.Range("A1").Resize(1, UBound(arrHeaders)).Value = arrHeaders
        
    For Each rngRow In rngData.Rows
            
        If rngRow.Row <> 1 Then
            
            With WsPayRates.Range("G" & Rows.Count).End(xlUp).Offset(1)
                .Offset(0, -4).Resize(8, 1).Value = WorksheetFunction.Transpose(WsInfo.Range("C1:J1").Value)
                rngRow.Offset(0, 2).Copy
                .Resize(rngRow.Columns.Count, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
                .Offset(0, -6).Resize(8, 2).Value = rngRow.Cells(1).Resize(1, 2).Value
                Application.CutCopyMode = False
                intRows = intRows + rngRow.Columns.Count
            End With
                        
        End If
        
    Next rngRow
       
    With WsPayRates.Range("A1").CurrentRegion
        With .Rows(1)
            .Interior.Color = RGB(213, 213, 213)
            .Font.Bold = True
        End With
        .Font.Size = 14
        .Font.Name = "Arial"
        .RowHeight = 30
        .VerticalAlignment = xlCenter
        .IndentLevel = 1
        .EntireColumn.AutoFit
    End With
    
    With WsPayRates.Range("A1").CurrentRegion.Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = vbBlack
    End With
    
    Q = Chr(34)
    
    strFormula = "=IF(LEFT($C2,7)=" & Q & "Mon-Fri" & Q & ",2,IF(LEFT($C2,3)=" & Q & "Sat" & Q & ",6,IF(LEFT($C2,3)=" & Q & "Sun" & Q & _
        ",1,IF(LEFT($C2,4)=" & Q & "Bank" & Q & "," & Q & "Bank" & Q & "," & Q & Q & "))))"
    With Range("D2").Resize(intRows, 1)
        .Formula2 = strFormula
        .Value = .Value
    End With
    
    strFormula = "=IF(MID($C2,5,3)=" & Q & "Fri" & Q & ",6,IF(LEFT($C2,4)=" & Q & "Bank" & Q & "," & Q & "Bank" & Q & ",D2))"
    With Range("E2").Resize(intRows, 1)
        .Formula2 = strFormula
        .Value = .Value
    End With
    
    strFormula = "=IF(ISNUMBER(FIND(" & Q & "Night" & Q & ",$C2))," & Q & "Night" & Q & "," & Q & "Day" & Q & ")"
    With Range("F2").Resize(intRows, 1)
        .Formula2 = strFormula
        .Value = .Value
    End With
    
    Range("A2").Select
    ActiveWindow.FreezePanes = True
    
    If WsInfo.Range("A1").Value <> "Agency Rates Paid" Then
        WsInfo.Rows(1).EntireRow.Insert
        WsInfo.Range("A1").Value = "Agency Rates Paid"
    End If
    
    ActiveWorkbook.Save
    
    MsgBox "'AgencyRatesPaid' worksheet created.", vbInformation, "Confirmation."
    
End Sub