I've trying to calculate the intervals for a workday, where the hours have different categories/earnings, and the standard hours are different depending on the days. I am using Excel 2010 for this, but I am hoping that once I figure out how this is done, it will be implementing as a part of the payroll system.
Standard hours, and the category is as follows:
Monday-thursday: 08.00 - 14.30 Category AA
Monday-thursday: 14:30 - 21:00 Category AB
Monday-thursday: 21:00 - 08:00 Category AC
Friday: 08.00 - 14:00 Category AA
Friday: 14:00 - 21:00 Category AB
Friday: 21.00 - 08:00 Category AC
Sat and Sun: Category AC
So what I am looking for is some method for doing the following.
If an employee works on the following days, with the given start, and lunch times
Day;WorkStart;WorkEnd;LunchStart;LunchEnd
Mon;18;04;23:30;00:00
Thu;06;15;11;11:30
Fri;08;17;12;12:30
Sat;05;12;09;09:30
Then I should get the following
Result:
Day;intervalStart;IntervalEnd;Category; HoursCount
Mon;18:00;23:30;AC;5,5;-- due to lunch being subtracted
Mon:00:00;04:00;AC;4
Thu;06:00;08:00;AC;2
Thu;08:00;14:30;AA;6 --due to lunch being subtracted
Thu;14:30;15:00;AB;0,5
Fri;08:00;14:00;AA;5,5; --due to lunch being subtracted
Fri;14:00;17:00;AB;3
Sat;05:00;12:00;AC;6,5;--due to lunch being subtracted
Mon: AC -> 9,5
Thu: AC -> 2
Thu: AB -> 0,5
Thu: AA -> 2
Fri: AA -> 5,5
Fri: AB -> 3
Sun: AC -> 6,5
I've tried looks ups based on the days, and then I end up with massive formula's that check for all the different cases, but it is unweidly, slow and it breaks if i make changes in the hours or something different.
I also tried different variations of tables but I keep getting the wrong answer due to friday being different and having to take into account that the category also switches when cross 21:00.
Because this works in intervals, I'd have to calculate across intervals, and that I haven't found a way of calculating yet, so I can assign a category to the different intervals across the day. While also managing that Friday is different, and the problem with rolling work-days.
I'd like to figure out how to do it in formula, but I am not ruling out the use of VBA for this. Ideally, when I have this prototype working, I can call for this to be implementing in the system that calculates Work hours and assigns pay.
Calculating overtime work is somewhat similar, but does not take into account the different intervals, or the rolling work-days.
I haven't found anything else that I can use. Perhaps it is because I am not familiar with this area so I can't quite put my finger on the proper search Words.
Here is a very basic example and will need to be expanded with a lot of additional code to handle day rollover and many other things but just to give you a concept
Class Category:
VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
END
Attribute VB_Name = "Category"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Private pweekday As String
Private ptime_start As Double
Private ptime_stop As Double
Private pcategory As String
Public Property Let weekday(day As String)
pweekday = day
End Property
Public Property Let time_start(time As Double)
If checkTime(time) Then
ptime_start = time
Else
MsgBox "Start Error"
End If
End Property
Public Property Let time_stop(time As Double)
If checkTime(time) Then
ptime_stop = time
Else
MsgBox "Stop Error"
End If
End Property
Public Property Let category(cat As String)
pcategory = cat
End Property
Public Sub setup(cat As String, day As String, t_start As Double, t_stop As Double)
If checkTime(t_start) And checkTime(t_stop) Then
pcategory = cat
pweekday = day
ptime_start = t_start
ptime_stop = t_stop
Else
MsgBox "SetupError"
End If
End Sub
Public Property Get category() As String
category = pcategory
End Property
Public Property Get weekday() As String
weekday = pweekday
End Property
Public Property Get time_start() As Double
time_start = ptime_start
End Property
Public Property Get time_stop() As Double
time_stop = ptime_stop
End Property
Private Function checkTime(time As Double) As Boolean
checkTime = time <= 24 And time >= 0
End Function
Class TimeTable
VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
END
Attribute VB_Name = "TimeTable"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Private ptime_table As New Collection
Public Sub add(cat As String, day As String, t_start As Double, t_stop As Double)
Dim addcat As New category
addcat.setup cat, day, t_start, t_stop
ptime_table.add addcat
End Sub
Public Function getStartCategory(day As String, t_start As Double, hours As Double) As String
Dim c As category
Dim c_start As String
Dim start_hours As Double
For Each c In ptime_table
If c.weekday = day Then
If t_start >= c.time_start And t_start <= c.time_stop Then
If hours > (c.time_stop - t_start) Then
start_hours = c.time_stop - t_start
Else
start_hours = hours
End If
c_start = CStr(start_hours) & ";" & c.category
End If
End If
Next c
getStartCategory = c_start
End Function
Public Function getStopCategory(day As String, t_stop As Double, hours As Double) As String
Dim c As category
Dim c_stop As String
Dim stop_hours As Double
For Each c In ptime_table
If c.weekday = day Then
If t_stop <= c.time_stop And t_stop >= c.time_start Then
If hours > (t_stop - c.time_start) Then
stop_hours = t_stop - c.time_start
Else
stop_hours = hours
End If
c_stop = CStr(stop_hours) & ";" & c.category
End If
End If
Next c
getStopCategory = c_stop
End Function
Public Function getCategory(day As String, t_start As Double, t_stop As Double) As String
Dim hours As Double
hours = hoursworked(t_start, t_stop)
c_start = getStartCategory(day, t_start, hours)
c_stop = getStopCategory(day, t_stop, hours)
getCategory = c_start & ";" & c_stop
End Function
Private Function hoursworked(t_start As Double, t_stop As Double) As Double
hoursworked = t_stop - t_start
End Function
Simple Example Module
Sub setup()
Dim tt As New TimeTable
With tt
.add "AA", "Monday", 8, 14.5
.add "AA", "Tuesday", 8, 14.5
.add "AA", "Wednesday", 8, 14.5
.add "AA", "Thursday", 8, 14.5
.add "AA", "Friday", 8, 14
.add "AB", "Monday", 14.5, 21
.add "AB", "Tuesday", 14.5, 21
.add "AB", "Wednesday", 14.5, 21
.add "AB", "Thursday", 14.5, 21
.add "AB", "Friday", 14, 21
.add "AC", "Monday", 21, 8
.add "AC", "Tuesday", 21, 8
.add "AC", "Wednesday", 21, 8
.add "AC", "Thursday", 21, 8
.add "AC", "Friday", 21, 8
.add "AC", "Saturday", 0, 24
.add "AC", "Sunday", 0, 24
End With
MsgBox tt.getCategory("Monday", 12, 18)
End Sub
This will Return "2.5;AA;3.5;AB" Meaning On Monday this person worked 2.5 hours in category AA and 3.5 hours in Category AB.
Like I said this is a simple example of how you might start and needs much more error handling, functionality and data mapping but hopefully it will get you started