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

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

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)

> ... Dan's formula will not work CORRECTED: Round DownInt(Year(Today()) - Year([Empl Svc Dt])) Round UpInt(Year(Today()) - Year([Empl Svc Dt]) + 1)

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.

How to calculate the number of years between two dates?

15 Replies

_anomDiebolt_
Qrew Elite
9 years ago
Round Down

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

Round Up

Int(Today() - Year([Empl Svc Dt]) + 1)
QuickBaseCoachD
Qrew Captain
9 years ago
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)
- StephanieHarris
  Qrew Assistant Captain
  7 years ago
  I am trying to do this however I don't need to know their actual years of service as today, only as of the date an incident was reported. Basically I need to know the years between Hire Date and Date Reported.
- QuickBaseCoachD
  Qrew Captain
  7 years ago
  Try this
  var date HireDate = [My Hire Date field];
  var date IncidentDate = [My Incident Field];
  
  Year($IncidentDate -Year($HireDate)
  -
  If(
  Month($IncidentDateToday()) < Month($HireDate$EST)
  or
  (Month($IncidentDate) = Month($IncidentDate) and Day($HireDate <Day($IncidentDate),1,0)
- StephanieHarris
  Qrew Assistant Captain
  7 years ago
  It's giving me a syntax error
_anomDiebolt_
Qrew Elite
9 years ago
> ... 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)
JeremiasBarroso
Qrew Member
9 years ago
Perfect! Thank you,
QuickBaseCoachD
Qrew Captain
9 years ago
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.
_anomDiebolt_
Qrew Elite
9 years ago
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.