Formula to Show Years and Months of Service

[Tophan]

I am trying to calculate years of service and would like to show months as a decimal. For instance, a person who joined the company in October of 1993 would have 22.2 years of service in December of 2015.


The problem I am encountering is if someone has left. I have a DateOfJoining column and a DateOfLeaving column. Using the formula below, the calculation is working except in those instances when a date is entered in the DateOfLeaving column.

=IF([@DateOfLeaving]=0,DAYS360([@DateOfJoining],TODAY())/360,DAYS360([@DateOfJoining],[@DateOfLeaving]/360))

Can someone suggest how to correct this formula to accept a date of leaving

 

[arnelgp]

Function fnAge2(dtmBD As Date, Optional dtmDate As Date = 0) _
 As Double
    ' Calculate a person's age, given the person's birth date and
    ' an optional "current" date.
    If dtmDate = 0 Then
        ' Did the caller pass in a date? If not, use
        ' the current date.
        dtmDate = Date
    End If
    fnAge2 = DateDiff("yyyy", dtmBD, dtmDate)
    If (dtmDate < DateSerial(Year(dtmDate), Month(dtmBD), _
      Day(dtmBD))) Then
      fnAge2 = fnAge2 + Round(DateDiff("m", dtmDate, DateSerial(Year(dtmDate), Month(dtmBD), Day(dtmBD))) / 12, 2)
    ElseIf (dtmDate > DateSerial(Year(dtmDate), Month(dtmBD), _
      Day(dtmBD))) Then
        fnAge2 = fnAge2 + Round(DateDiff("m", DateSerial(Year(dtmDate), Month(dtmBD), Day(dtmBD)), dtmDate) / 12, 2)
    End If
End Function

 

[Tophan]

Thanks but needed a simpler solution. Checked my formula again and realized the error - just a syntax error. The below formula is working perfectly and giving a result when date of leaving is added

=IF([@DateOfLeaving]=0,DAYS360([@DateOfJoining],TODAY())/360,DAYS360([@DateOfJoining],[@DateOfLeaving])/360)