Requesting Help with Formula - Employment Time Accrual

  • Thread starter Thread starter Marcia
  • Start date Start date
M

Marcia

Hello. I need to be able to track the total amount of time that
employees accumulate throughout their years of service, and display
the result in terms of YEARS and MONTHS. My fields include
fldStartDate and fldEndDate.

I don't think it is as simple as subtracting the two dates because of
the complex way that the company uses to determine whether the month
of the StartDate and EndDate will be included as part of the
employee's service record. For example, for the months of the
StartDate or the EndDate, they:

1. Count the number of Weekdays in that month.
2. Divide by 2 (don't round up – eg., 23 days / 2 = 11).
3. Starting on the last Weekday of that month, begin counting
backward (Weekdays only).
4. When you get to the halfway point (ie., 11 in this case), you have
reached the day by which the employee must have started/ended work for
that month to be included as part of his service record.

Does anyone know how to do this with a formula?

Thanks!
Jessi
 
Hi Marcia

Here is a formula that will calculate the number of workdays in a certain
period of time..however this does not account for holidays.

For items 2,3 and 4, I have also included the Median function that will allow
you find the halfway point in a series. With these two functions you should be
able to calculate the required information.

Hope this helps.

Best regards

Maurice St-Cyr

Micro Systems Consultants, Inc.
---------------------------------------------------


Function Work_Days (BegDate As Variant, EndDate As Variant) As Integer
' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

BegDate = DateValue(BegDate)

EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt < EndDate
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function
-----------------------------------------

Function Median (tName$, fldName$) As Single
Dim MedianDB As Database
Dim ssMedian As Recordset
Dim RCount%, i%, x%, y%, OffSet%
Set MedianDB = CurrentDB()
Set ssMedian = MedianDB.Openrecordset("SELECT [" & fldName$ & _
"] FROM [" & tName$ & "] WHERE [" & fldName$ & "] IS _
NOT NULL ORDER BY [" & fldName$ & "];")
'NOTE: To include nulls when calculating the median value, omit
'WHERE [" & fldName$ & "] IS NOT NULL from the example.

ssMedian.MoveLast
RCount% = ssMedian.RecordCount
x% = RCount% Mod 2
If x% <> 0 Then
OffSet% = ((RCount% + 1) / 2) - 2
For i% = 0 To OffSet%
ssMedian.MovePrevious
Next i
Median = ssMedian(fldName$)
Else
OffSet% = (RCount% / 2) - 2
For i% = 0 To OffSet%
ssMedian.MovePrevious
Next i
x% = ssMedian(fldName$)
ssMedian.MovePrevious

y% = ssMedian(fldName$)
Median = (x% + y%) / 2
End If
ssMedian.Close
MedianDB.Close
End Function

===========================
 
Back
Top