Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function that groups together Overtime hours by 7.5 hrs
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function that groups together Overtime hours by 7.5 hrs
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? "Jan T." wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function that groups together Overtime hours by 7.5 hrs
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Work hours with overtime | Excel Discussion (Misc queries) | |||
overtime hours formula | Excel Worksheet Functions | |||
regular and overtime hours | Excel Worksheet Functions | |||
Overtime Hours | Excel Worksheet Functions | |||
need help w/formula for calculating overtime hours | Excel Worksheet Functions |