calculating from a date field

[strugglingDave]

Have a date field (ex. 11/01/2000) that represents hire date.
I would like to be able to calculate years of service using todays date and the hire date field. For example, using the date above, I would like it to tell me that I have been employed for 3 years now. I am not concerned with days.

 

[GumbyD]

Try the following:

DateDiff("yyyy",[HireDate],date())

GumbyD

 

[strugglingDave]

That worked thanks. Just for future reference, how would I include days? FOr example 1 year and 200 days.

 

[GumbyD]

After I looked again I realized that my original expression has some problems. Try this one instead:

IIf(DateSerial(Year(Date()),Month([Hiredate]),Day([Hiredate]))>Date(),DateDiff("yyyy",[HireDate],Date())-1,DateDiff("yyyy",[HireDate],Date()))

I will enter another entry for the years and days one in a few minutes.

GumbyD

 

[GumbyD]

Try this one for years and days:

IIf(DateSerial(Year(Date()),Month([Hiredate]),Day([Hiredate]))>Date(),DateDiff("yyyy",[HireDate],Date())-1 & " years " & DateDiff("d",DateSerial(Year(Date())-1,Month([Hiredate]),Day([hiredate])),Date()) & " days",DateDiff("yyyy",[HireDate],Date()) & " years " & DateDiff("d",DateSerial(Year(Date()),Month([Hiredate]),Day([hiredate])),Date()) & " days")

GumbyD

 

[strugglingDave]

Thanks.