Forum Discussion

JohnDonnelly's avatar
JohnDonnelly
Qrew Trainee
8 years ago

Formula to show duration between 2 dates in order to calculate how long employee has been employed

I would like to show the amount of time an employee has been employed.  We have the following date fields;

[Date of Hire]

[Termination Date]

as well as a checkbox field ([Active]) for if they are active.

If they have been terminated, it should be termination date-date of hire, but if they are still active, we would like it to be today()-date of hire.

I do not know if the formula field should be Numeric or Duration, but we prefer the end number to be in years (1.5, 3.1, 0.8, etc.).


App builders
Ask about Quickbase functionality
Formulas and functions
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    Not tested but try this as a formula Numeric field called [Service Years]


    var date EndDate = IF(IsNull([Termination Date],Today(),[Termination Date]);

    ToDays($EndDate - [Date of Hire]) / 365.25
    • JohnDonnelly's avatar
      JohnDonnelly
      Qrew Trainee
      8 years ago

      Thank you for the quick response. I receive the following syntax error with the formula above;

      Formula syntax error

      Please check the syntax of your entire formula. Possible culprits are a mismatched set of parentheses, missing quotes, or an extra bracket.

      var date EndDate = IF(IsNull([Termination Date],Today(),[Termination Date])  ;

      ToDays($EndDate - [Date of Hire]) / 365.25
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      8 years ago
      Missing a bracket

      try this

      var date EndDate = IF(IsNull([Termination Date]),Today(),[Termination Date]);

      ToDays($EndDate - [Date of Hire]) / 365.25