Help calculating time passed

[JL]

Hi all,



I hope someone can help me with this.



I have an Excel XP worksheet that I use to calculate both age and time
passed since hiring.



I use the function below for both



Option Explicit



' Function to calculate a person's age.

'

Function Age(DoB As Date)

If DoB = 0 Then

Age = ""

Else

Select Case Month(Date)

Case Is < Month(DoB)

Age = Year(Date) - Year(DoB) - 1

Case Is = Month(DoB)

If Day(Date) >= Day(DoB) Then

Age = Year(Date) - Year(DoB)

Else

Age = Year(Date) - Year(DoB) - 1

End If

Case Is > Month(DoB)

Age = Year(Date) - Year(DoB)

End Select

End If

End Function



However for calculating the amount of time passed since hiring I receive a
number only. For example if they were hired one year and 11 months ago I
still receive a 1 because 2 years has not been reached. The above function
works great on the Date of birth.



Can anyone suggest another function that would give me years and month's
since being hired?

I would like it formatted in a decimal if possible such as 2.6 for two years
and six months.



The setup is that in column E I have the date hired entered and column F I
have the time since hired.





Thanks for your help.



Lou

 

[Jim Thomlinson]

As a guess you need the undocumented DateDif function. Check out this link...

http://www.cpearson.com/excel/datedif.htm

 

[Bob Phillips]

Function Age(DoB As Date)

If DoB = 0 Then
Age = ""
Else
Select Case Month(Date)
Case Is < Month(DoB)
Age = Year(Date) - Year(DoB) - 1 + _
(Month(Date) + 12 - (Month(DoB))) / 12
Case Is = Month(DoB)
If Day(Date) >= Day(DoB) Then
Age = Year(Date) - Year(DoB)
Else
Age = Year(Date) - Year(DoB) - 1
End If
Case Is > Month(DoB)
Age = Year(Date) - Year(DoB) + _
(Month(Date) - (Month(DoB))) / 12
End Select
End If

End Function


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[JL]

You guys are great. Thanks

JL

Function Age(DoB As Date)

If DoB = 0 Then
Age = ""
Else
Select Case Month(Date)
Case Is < Month(DoB)
Age = Year(Date) - Year(DoB) - 1 + _
(Month(Date) + 12 - (Month(DoB))) / 12
Case Is = Month(DoB)
If Day(Date) >= Day(DoB) Then
Age = Year(Date) - Year(DoB)
Else
Age = Year(Date) - Year(DoB) - 1
End If
Case Is > Month(DoB)
Age = Year(Date) - Year(DoB) + _
(Month(Date) - (Month(DoB))) / 12
End Select
End If

End Function


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[JL]

You guys are great. Thanks

JL

As a guess you need the undocumented DateDif function. Check out this
link...

http://www.cpearson.com/excel/datedif.htm