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

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered

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.).


Photo of John

John

  • 60 Points

Posted 1 year ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,006 Points 50k badge 2x thumb
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
Photo of John

John

  • 60 Points

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

QuickBaseCoach App Dev./Training, Champion

  • 51,006 Points 50k badge 2x thumb
Missing a bracket

try this

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

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

John

  • 60 Points

That works perfectly, thank you so much.