Timesheet formula

  • Thread starter Thread starter Jose Anibal
  • Start date Start date
J

Jose Anibal

Hi
I have a form (Access 2007) where I enter weekly hours (present and absent)
for 60 employees. Every employee accumulates up to but not more than 480
hours at any moment during his employment period, (accumulates 12 hours every
month). If his balance is 480 sick live hours and gets absent, lets say, 32
hours during this week, then his sick leave balance should be 460 hours
([480-32]+[12] at the end of the month. If the employee is never absent, then
his sick leave balance should be no more than 480 hours. If he is absent 8
hours this week, then his balance should be 480 [480-8]+[12].
How can this be done in the form? What formula can I place in the query
to obtain the same information as above? Any help will be greatly
appreciated. Thanks in advance.
 
You might be able to use one or both of the following formulas.

Usage would be like:

[LeaveBalance] = MinOf(480, ([LeaveBalance] - [SickTaken]) +
[AccumulatedLeave])

Code Start
==================
Public Function MinOf(Value1 As Variant, Value2 As Variant) As Variant
Const PROC_NAME As String = "MinOf"

On Error GoTo ErrorHandler

If Value1 < Value2 Then
MinOf = Value1
Else
MinOf = Value2
End If

Cleanup:
Exit Function

ErrorHandler:
MsgBox "Error: " & Err.Number & ", " & Err.Description, , MOD_NAME & "."
& PROC_NAME

On Error Resume Next

GoTo Cleanup

End Function


Public Function MaxOf(Value1 As Variant, Value2 As Variant) As Variant
Const PROC_NAME As String = "MaxOf"

On Error GoTo ErrorHandler

If Value1 > Value2 Then
MaxOf = Value1
Else
MaxOf = Value2
End If

Cleanup:
Exit Function

ErrorHandler:
MsgBox "Error: " & Err.Number & ", " & Err.Description, , MOD_NAME & "."
& PROC_NAME

On Error Resume Next

GoTo Cleanup

End Function

==================
Code End
 
Back
Top