Calculate Experience

[thechazm]

Good Day All,

I am looking for a little help on creating an good solution for calculating out someones experience based on a specific start date.

I've created some code to start the process but it's still not quite right. Here is the code:

' Format Experience field
        eYears = DateDiff("yyyy", rs("Experience"), evalDate)
        eMonths = DateDiff("m", rs("Experience"), DateAdd("yyyy", eYears * -1, evalDate))
        eMonths = Abs(eMonths)
        evalDate = DateAdd("yyyy", eYears * -1, evalDate)
        eDays = DateDiff("d", rs("Experience"), DateAdd("m", eMonths * -1, evalDate))
        eDays = Abs(eDays)
        
        Experience = eYears & " years, " & eMonths & " months, " & eDays & " days"

Any input/help is greatly appreciated.

 

[bob fitz]

Perhaps the following code will help:

Public Function CalculateLongAge(DOB As Date, CurDate As Date) As String
    Dim Y As Integer
    Dim M As Integer
    Dim D As Integer
    Dim Temp1 As Date
    Temp1 = DateSerial(Year(CurDate), month(DOB), Day(DOB))
    Y = Year(CurDate) - Year(DOB) + (Temp1 > CurDate)
    M = month(CurDate) - month(DOB) - (12 * (Temp1 > CurDate))
    D = Day(CurDate) - Day(DOB)
    If D < 0 Then
        M = M - 1
        D = Day(DateSerial(Year(CurDate), month(CurDate) + 1, 0)) + D + 1
    End If
    CalculateLongAge = Y & " years " & M & " months " & D & " days"
End Function

I take no credit for the actual code, which I found on the web some time ago. Not sure but I think I found it on a Microsoft site.

 

[thechazm]

After reviewing your code it reminded of some code I had ran accross in the past so I ended up using that but your help and time were greatly appreciated. Solution below (I take no credit since it's not my code please thank Sal Ricciardi who made the post here: http://msdn.microsoft.com/en-us/library/dd327646(v=office.12).aspx)

[My little piece of code]

eDays = Workdays(rs("Experience"), evalDate)
eYears = Fix(eDays / 365)
eDays = eDays - (eYears * 365)
eMonths = Fix(eDays / 30)
eDays = eDays - (eMonths * 30)

Experience = eYears & " years, " & eMonths & " months, " & eDays & " days"

[Sal Ricciardi's functional code - I only commented out the holiday lines since I am not going to use that ability right now]

Function Workdays(ByRef startDate As Date, ByRef endDate As Date, Optional ByRef strHolidays As String = "Holidays") As Integer
    
    On Error GoTo Workdays_Error
    Dim nWeekdays As Integer
    Dim nHolidays As Integer
    Dim strWhere As String
    
    ' DateValue returns the date part only.
    startDate = DateValue(startDate)
    endDate = DateValue(endDate)
    
    nWeekdays = Weekdays(startDate, endDate)
    If nWeekdays = -1 Then
        Workdays = -1
        GoTo Workdays_Exit
    End If
    
    'strWhere = "[Holiday] >= #" & startDate & "# AND [Holiday] <= #" & endDate & "#"
    
    ' Count the number of holidays.
    'nHolidays = DCount(Expr:="[Holiday]", Domain:=strHolidays, Criteria:=strWhere)
    
    Workdays = nWeekdays
    '- nHolidays
    
Workdays_Exit:
    Exit Function
    
Workdays_Error:
    Workdays = -1
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
        vbCritical, "Workdays"
    Resume Workdays_Exit
    
End Function
Function Weekdays(ByRef startDate As Date, ByRef endDate As Date) As Integer
    On Error GoTo Weekdays_Error
    Const ncNumberOfWeekendDays As Integer = 2
    Dim varDays As Variant
    Dim varWeekendDays As Variant
    Dim dtmX As Date
       
    If endDate < startDate Then
        dtmX = startDate
        startDate = endDate
        endDate = dtmX
    End If
    
    ' Calculate the number of days inclusive (+ 1 is to add back startDate).
    varDays = DateDiff(Interval:="d", Date1:=startDate, Date2:=endDate) + 1
    
    ' Calculate the number of weekend days.
    varWeekendDays = (DateDiff(Interval:="ww", Date1:=startDate, Date2:=endDate) * ncNumberOfWeekendDays) + IIf(DatePart(Interval:="w", Date:=startDate) = vbSunday, 1, 0) _
        + IIf(DatePart(Interval:="w", Date:=endDate) = vbSaturday, 1, 0)
    
    ' Calculate the number of weekdays.
    Weekdays = (varDays - varWeekendDays)
    
Weekdays_Exit:
    Exit Function
    
Weekdays_Error:
    Weekdays = -1
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
        vbCritical, "Weekdays"
    Resume Weekdays_Exit
End Function

Hope this helps someone else out.

Have a good one,