Function that groups together Overtime hours by 7.5 hrs

[Jan T.]

Working overtime, there are two options. Get extra money
or have some extra days off later.

The table below shows Overtime worked in May.

Line Date Hrs
---------------------
1 05/01 3
2 05/02 1,5
3 05/05 2
4 05/06 4
5 05/07 1
6 05/08 2
7 05/10 8

What I want to do here, is take the overtime out
in free time, 2 days in august (Aug the 1. and 2.)
in stead of getting extra paid on my salary.

I will have a wizard that asks for a Start date where
I will write aug.1. Duration; 2 days. What I now
want is a function that groups together hours in
work days with 7.5 hrs a day (full time) by setting
vacation date in the last column.

As you can see under neeth, I have done this manually.
It is easy math when you do it manually.
However I want to have a function to do this automatically.


Line Date Hrs Used for vacation
-----------------------------------------
1 05/01 3 08/01
2 05/02 1,5 08/01
3 05/05 2 08/01
4 05/06 4 08/02
5 05/07 1 08/01
6 05/08 2 08/02
7 05/10 8 (need to split this first - se below)
8 a function takes care of this an adds:
9 05/10 1,5 08/02
10 05/10 6,5 overtime salary

The challenge is to pick the rows that makes up the sum 7.5 hrs
that is one work day, without having to split moore rows than
necessary. Then I put the date for vacation in the last column.

The function will have to continue until all rows needed do have
a date for vacation. The left overs, 6.5 hrs will be used another
time or it can be paid out as overtime. Got it?

One strategy could be to start with the lowest amount of hrs and write
the aug.1 then
add the second lowest number of hrs and so on until it reaches
7.5. But that is not always the best solution I quess. Say I
picked 4, 1.5 and 2. That would be exactly 7.5 hrs even I did not
pick all the smalest numbers (hrs).

How can I write an optimal function here?
Any suggestions for an optimal solution (function) to this challenge?

Thank you very much for helping.

Regards
Jan

 

[David]

Hi,
It is hard to tell what is happening here, maybe that is why you are not
getting answers so far. This is a single employee, I think? How is August
picked to do the comparison? Is this a choice for the employee? He/she can
choose pay or time off? The last one presented, 8 hours, why is it not 7.5
hours off(PTO) and 1/2 hour paid overtime(OT)?
It almost appears that any time over 7.5 is paid time and up to 6.5 is PTO,
so a simple formula would do, but maybe I am missing some thing? None of the
time is a choice?

 

[merjet]

The Sub below should get you started. Run it more than once for more
than one vacation day. Nor will it grab OT if there are more than, but
not exactly equal, 7.5 hours available. In other words, it won't split
days. Frankly, if you are going to split days, you may as well not
skip days either. In other words, in your example split line 4 rather
than using line 5. If you still want to split days, you can modify the
Sub below.

Hth,
Merjet

Sub GatherOT()
Dim iRow As Integer
Dim iRow2 As Integer
Dim iPos As Integer
Dim dtDay As Date
Dim aRows As String
Dim sSum As Single

Do
iRow = iRow + 1
If Cells(iRow, 4) = "" Then
If sSum + Cells(iRow, 3) <= 7.5 Then
sSum = sSum + Cells(iRow, 3)
aRows = aRows & "x" & iRow
End If
End If
If sSum = 7.5 Then
dtDay = InputBox("Enter vacation day (mm/dd).")
Do
aRows = Right(aRows, Len(aRows) - 1)
iPos = InStr(aRows, "x")
If iPos = 0 Then
iRow2 = aRows
aRows = ""
Else
iRow2 = Left(aRows, iPos - 1)
aRows = Right(aRows, Len(aRows) - iPos + 1)
End If
Cells(iRow2, 4) = dtDay
Loop Until aRows = ""
Exit Sub
End If
Loop Until Cells(iRow + 1, 1) = ""
End Sub