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

 

[shell2133]

Thank you ever so much for your time so far. I think I've followed what you've done above but I'm unsure how to calculate the cost when a shift runs over into night rate e.g. for this line, the 30 minutes worked from 8pm-8.30pm would be at night rate, the rest at day rate. How would I work this out please?

Person

Agency Name

Employee Grade

Start Date

Start Time

End Date

End Time

Break Time

Break From

Break To

Cost

First Day Hours

HoursBeforeMidnight

HoursAfterMidnight

E Jones

AG5

1​

01/04/2023​

08:30:00​

01/04/2023​

20:30:00​

01:00​

12:30:00​

13:30:00​

12​

0​

0​

 

[Herakles]

I've sorted that but I need to know how midnight is represented in the End Time column.

Is it 00:00:00 but that is a unique transitional moment that doesn't really belong to either the day before or the day after.

The normal way of getting around the issue is to simply use 23:59:59 but how we calculate hours worked on your spreadsheet does depend on
what you inherit. I could use 23:59:59 in the formula when 00:00:00 has been recorded.

Using 00:00:00 as a starting time presents no problem.

 

[shell2133]

It is 00:00:00 on the data I receive but it's no issue to change any with that time to 23:59:59 if its easier, whatever works best is fine.

 

[awoohaw]

@shell2133 , when you wrote holiday rates supplant regular rates, does that mean if any time is worked on holiday the holiday rate applies for all hours worked?

 

[shell2133]

Yes, any hours worked on any of the 8 bank holiday dates would be at the bank holiday rate.

 

[awoohaw]

Yes, any hours worked on any of the 8 bank holiday dates would be at the bank holiday rate.

that doesn't really answer my question though.
I'm asking if someone works 8 pm to 4 am... and either 8-12 or 12-4 is a holiday is that 8 hours at holiday rate, or is all time paid based on the pure calendar date?

 

[shell2133]

Based purely on the calendar date

 

[Herakles]

Can you give these a go.
Are you happy to concert the rates data using the code that I supplied earlier.
It would make calculating the costs much easier.

Success depends on the data that you are given adhering to certain rules.
A check could be made that it does before you process it.

The time of 00:00:00 causes a problem so needs to be replaced with 23:59:59.
Some VBA code can be written to do this.

Column L - 00:00 to 07:00
=fncGetHoursWorked(IF($G2<$E2,TIME(0,0,0),$E2),$G2, TIME(0, 0, 0), TIME(7, 0, 0),FALSE)

Column M - 07:00 to 20:00
=fncGetHoursWorked($E2,IF($D2<>$F2,TIME(20,0,0),$G2), TIME(7, 0, 0), TIME(20, 0, 0),FALSE)

Column N - 20:00 to 23:59:59
=fncGetHoursWorked($E2,IF($G2<$E2,TIME(23, 59,59),$G2), TIME(20, 0, 0), TIME(23, 59,59),FALSE)

The fncGetHoursWorked function that us called from the three columns is in the code below.
Copy and paste this into a standard code module before you copy and paste the above formulas into the worksheet.

Public Function fncGetHoursWorked(dteStartTime As Date, dteEndTime As Date, dteShiftStart As Date, dteShiftEnd As Date, blnEarlier As Boolean)
Dim dblHours As Double
    
    If Not (dteStartTime > dteShiftEnd Or dteEndTime < dteShiftStart) Then
        dblHours = (Application.WorksheetFunction.Min(dteEndTime, dteShiftEnd) - Application.WorksheetFunction.Max(dteStartTime, dteShiftStart))
    End If

    fncGetHoursWorked = Application.WorksheetFunction.Ceiling(dblHours * 24, 0.01)

End Function

 

[shell2133]

Thank you for this, that works perfectly How would I then work out the cost of the hours in L-N please?

 

[Herakles]

Apologies for the delay.

Have you split the rates of pay grid using the code that I posted.

Also, are the bank holidays in a seperate sheet?

This will make the next stage easier.