# How to calculate the number of years between two dates?

• 0
• Question
• Updated 2 years ago

I have a date field to indicate an employee's start date called "Empl Svc Dt". The field is used to determine the number of years an employee has been employed. Does anyone know the best way to calculate the number of year(s) as a single number  "0, 1, 2" etc. between the current date and "Empl Svc Dt"?

• 0 Points

Posted 4 years ago

• 0

Ⲇanom the ultimate (Dan Diebolt), Champion

• 30,224 Points
Round Down

Int(Today() - Year([Empl Svc Dt]))

Round Up

Int(Today() - Year([Empl Svc Dt]) + 1)
• 72,488 Points
Try this.  i suspect that Dan's formula will not work

This formula was for Age, but I changed it to be used for completed years of service.

var date EST = [
Empl Svc Dt];

Year(Today())-Year(\$EST)
-
If(
Month(Today())<Month(\$EST)
or
(Month(Today())=Month(\$EST) and Day(Today())<Day(\$EST)),1,0)
• 732 Points

var date HireDate = [Employee Adjusted Service Date];

var date IncidentDate = [Date Reported];

Year(\$IncidentDate) -Year(\$HireDate)

-

If(Month(\$IncidentDate) < Month(\$HireDate)

or

Month(\$IncidentDate) = Month(\$IncidentDate) and Day(\$HireDate) <Day(\$IncidentDate),1,0)

Thanks!

(Edited)
• 72,488 Points
OK, thx for letting me know.
• 732 Points
I just noticed this is giving me a negative 1, I expected this for when the Date Reported was before the Employee Adjusted Service Date, however this is also happening in instances such as Date Reported = 5/23/17 and Employee Adjusted Hire Date = 1/15/2017
• 72,488 Points
You can put a floor of zero on the result.

var date HireDate = [Employee Adjusted Service Date];

var date IncidentDate = [Date Reported];

var number RawCalc = Year(\$IncidentDate) -Year(\$HireDate)

-

If(Month(\$IncidentDate) < Month(\$HireDate)

or

Month(\$IncidentDate) = Month(\$IncidentDate) and Day(\$HireDate) <Day(\$IncidentDate),1,0);

Max(0, \$RawCalc)
• 732 Points
This field is currently Formula Numeric, I'd like to add that if [Employee Adjusted Service Date] is Null this field reflects as "Unknown".  I tried the following but it doesn't work.  Any suggestions?

var date HireDate = [Employee Adjusted Service Date];

var date IncidentDate = [Date Reported];

var number RawCalc = Year(\$IncidentDate) -Year(\$HireDate)

-

If(Month(\$IncidentDate) < Month(\$HireDate)

or

Month(\$IncidentDate) = Month(\$IncidentDate) and Day(\$HireDate) <Day(\$IncidentDate),1,0));

Max(0, \$RawCalc)

Ⲇanom the ultimate (Dan Diebolt), Champion

• 30,224 Points
> ... Dan's formula will not work

CORRECTED:

Round Down
Int(Year(Today()) - Year([Empl Svc Dt]))

Round Up
Int(Year(Today()) - Year([Empl Svc Dt]) + 1)
• 0 Points
Perfect! Thank you,
• 72,488 Points
Dan,
So if they hire me on December 30th and the Vacation Policy is that I get 2 weeks vacation after 1 complete year of service, then on January 2nd, 3 days later, the your formula says I get my 2 weeks vacation.  Sweet.  I'd like to work for that Company.

Ⲇanom the ultimate (Dan Diebolt), Champion

• 30,224 Points
Yeah I am very liberal in that regard. Also you should be implementing your own 20% program to work on whatever you want irrespective of whether or not you company has a 20% program. Slack off.