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 calcu...
roles and permissions
Tables and fields
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
    9 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
    9 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's avatar
    StephanieHarris
    Qrew Assistant Captain
    9 years ago
    It's giving me a syntax error
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    9 years ago
    Corrected:
    var date HireDate = [My Hire Date field];
    var date IncidentDate = [My Incident Field];


    Year($IncidentDate -Year($HireDate)
    -
    If(
    Month($IncidentDate) < Month($HireDate)
    or
    (Month($IncidentDate) = Month($IncidentDate) and Day($HireDate <Day($IncidentDate),1,0)
  • StephanieHarris's avatar
    StephanieHarris
    Qrew Assistant Captain
    9 years ago

    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!

  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    9 years ago
    OK, thx for letting me know.
  • StephanieHarris's avatar
    StephanieHarris
    Qrew Assistant Captain
    9 years ago
    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
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    9 years ago
    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)
  • StephanieHarris's avatar
    StephanieHarris
    Qrew Assistant Captain
    9 years ago
    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)