timesheet Out of Hours enhanced payment values

[BobFossil]

Hello VBA Express, i hope you can help with this. I have to compile 'hours worked' for members of staff in a healthcare environment. However, the situation is complicated by 'when' in both hours and date range that a member of staff works and i am stumped as to how to calculate the numbers without a lot of manual sifting.

My core (or normal) working week hours are 08:00:00 AM to 20:00:00 PM Monday to Friday. Any calls or visits made between 20:00:00 PM and 08:00:00 AM attract an enhanced pay level, similarly Weekends are from 20:00:00 PM on Friday to 08:00:00 AM on Monday and attract this enhancement as well. I also have to consider Bank Holidays but i am ok with this aspect.

What i can't figure out how to do is calculate the 'normal' hours and 'enhanced payment' hours. On-call or emergency visits don't have any set pattern and can be a combination of normal/enhanced or workday/weekend/Bank Holiday

If a member of staff starts work at 19:30 pm and finishes at 22:00:00 pm, that is half a 'normal' hour and 2 enhanced hours.
If a member of staff starts at 03:15:00 am and finishes at 09:45 am, that is 4.75 enhanced hours and 1.75 normal hours.

Where hours are solely within either category, Normal or Enhanced, the formula is relatively simple, it is the cross over values that i can't figure out the formula for. I have attached a sample of the timesheet data

i am hoping that one of you will know of a procedure or technique for doing this with a VBA Macro or somesuch as i can't get this last piece of the puzzle. I have on average 4000 rows of work hours to calculate each month so would be grateful if you can help at all.

Regards,

Rob

[SamT]

That file is big. And Read Only

Can you delete all but 20 rows of info on the Sheet "Data". Then close Excel before you upload the book.

Thanks

[BobFossil]

Hi, apologies, didn't think about file size, new version attached.

Regards,

[SamT]

I didn't go any farther because I don't really k now what you need. IOW, how much of the data on Sheets("DATA") is just used to help find the required info on Sheets("pivot") and how much do you need ellewhere?

All these function only use columns C and F on Sheets("DATA")

Option Explicit

Public Const BankHolidaysSheet As String = "Sheet2"

Public Function IsBankHoliday(aDate As Date) As Boolean
Static BankHolidays As Variant
Dim i As Long

If Not IsArray(BankHolidays) Then
  With Sheets(BankHolidaysSheet)
    BankHolidays = Range(.Range("a1"), .Range("A1").End(xlDown)).Value
  End With
End If

For i = LBound(BankHolidays) To UBound(BankHolidays)
  If BankHolidays(i, 1) = aDate Then
    IsBankHoliday = True
    Exit Function
  End If
Next i
End Function

Public Function IsWeekEnd(aDate As Date) As Boolean

IsWeekEnd = (Weekday(aDate) = 1 Or Weekday(aDate) = 7)
End Function

Public Function IsDuringRegularHours(aDateTime As Date) As Boolean
Const StartTime As Double = 0.333333333333333     'TimeValue("08:00:00")
Const QuitTime As Double = 0.833333333333333 'TimeValue("20:00:00")

Dim aTime As Double
aTime = TimeValue(aDateTime)

IsDuringRegularHours = (aTime >= StartTime And aTime < QuitTime)

End Function

Public Function AllHours(StartTime As Date, QuitTime As Date) As String
Dim TotHrs As Double
Dim Hrs As String
Dim Mins As Variant

TotHrs = (QuitTime - StartTime) * 24
Hrs = WorksheetFunction.RoundDown(TotHrs, 0)
Mins = (TotHrs * 60) Mod 60

If Mins < 10 Then Mins = "0" & Mins

AllHours = Hrs & ":" & Mins

End Function

ATT, it looks like the only thing left is to determine Core hours. But, I bet you need yet more categories of hours broken out.

[Paul_Hossler]

1. What does each row represent? Guessing employees-days worked??

2. It seems that there's a lot of intermediate values being used to come up with a CATEGORY = "EEWKNDWKNDSD" for example. Do you really need those intermediates at the end?

3. It seems that the independent variables are Start Date/Time and End Date/Time, and what you ultimately want is the number of regular and enhanced hours worked (for each employee), but I didn't see a name or ID

If I'm even close, the way I'd capture your business rules is with a user defined function that takes StartDateTime, EndDateTime, HolidaysArray as inputs and Regular and Enhanced as outputs

[SamT]

Thanks, Paul. You speak human much better than I.





Bob, the Input data we need is the start/stop times, the BH table and the Core/Enhanced times. We have that

The next thing we need to know are all the final outputs, without any intermediate steps.

One thing I failed to account for is when work period starts and stops in extended time yet includes an entire Core period,ie an 18 hour shift (04:00 t0 22:00)

[BobFossil]

Hello Paul,

Thank you for responding.
1. each row is a 'work period and can be anything from half an hour to 12 hours or more.
2. I am generating the 'categories' as i was attempting to create formulas within a massive if statement particular to each category. The intermediates tell me what the formula needs to do.
3. i stripped the names and i.d.'s out as we work in a sensitive environment and it is company policy when sending data externally, however, i could have used dummy data. but you are quite correct, "what you ultimately want is the number of regular and enhanced hours worked (for each employee)"

Finally, you are close but i am such a newbie with anything other than the most basic VBA that i don't have any idea how to create 'a user defined function'. With your knowledge, would i be able to achieve what i need using a (complicated i know) nested if statement?

Rob

[BobFossil]

Hi SamT, thank you, i can see where your code is taking me, but as i explained to Paul, my knowledge of VA is pretty thin. The final outputs are simply (he says) the number of core hours/minutes and number of enhanced hours/minutes for each row. There will always be an oddity such as you describe, but given that currently this whole process is done using some basic excel filtering and manually examining the row, if we had to examine a small number individually that would still be a massive time saving for us.

Rob

[SamT]

If you are good with formulas, each of those public Functions is available to Cell formulas. For example: The Formula in U5 would be :

IF(IsWeekEnd(C5),"WKND",IF(IsBankHoliday(C5),"BH,"WKDY"))

The Formula in V5 would be:

IF(IsWeekEnd(F5),"WKND",IF(IsBankHoliday(F5),"BH,"WKDY"))

Except the Date values in Column F are Incomplete/Bad.

Then you could copy them down like any other formula.

However, to complete the required UDFs, I need to know. . .

On this side of the pond we have OverTime pay. What needs to happen in this workbook when, for instance, a shift starts Sunday at noon and ends 24 hours later, ie, 4 hours into a Core period?