How to calculate the number of years between two dates?

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered

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"?

Photo of Jerry

Jerry

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,044 Points 20k badge 2x thumb
Round Down

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

Round Up

Int(Today() - Year([Empl Svc Dt]) + 1)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
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)
Photo of Stephanie

Stephanie

  • 732 Points 500 badge 2x thumb

Worked with a slight adjustment:


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)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
OK, thx for letting me know.
Photo of Stephanie

Stephanie

  • 732 Points 500 badge 2x thumb
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
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
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)
Photo of Stephanie

Stephanie

  • 732 Points 500 badge 2x thumb
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(IsNull([Employee Adjusted Service Date]), "Unknown",

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

 

or

 

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

 

Max(0, $RawCalc)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,044 Points 20k badge 2x thumb
> ... 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)
Photo of Jerry

Jerry

  • 0 Points
Perfect! Thank you,
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
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.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,044 Points 20k badge 2x thumb
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.