Forum Discussion

JeremiasBarroso's avatar
JeremiasBarroso
Qrew Member
10 years ago

How to calculate the number of years between two dates?

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

roles and permissions
Tables and fields
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    10 years ago
    Round Down

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

    Round Up

    Int(Today() - Year([Empl Svc Dt]) + 1)
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    10 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's avatar
      StephanieHarris
      Qrew Assistant Captain
      8 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's avatar
      QuickBaseCoachD
      Qrew Captain
      8 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)
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    10 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)
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    10 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_'s avatar
    _anomDiebolt_
    Qrew Elite
    10 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.