Discussions

Expand all | Collapse all

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

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

    Posted 07-18-2017 20:33

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




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

    Posted 07-18-2017 21:41
    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


  • 3.  RE: Formula to show duration between 2 dates in order to calculate how long employee has been employed

    Posted 07-18-2017 21:51

    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


  • 4.  RE: Formula to show duration between 2 dates in order to calculate how long employee has been employed

    Posted 07-18-2017 21:52
    Missing a bracket

    try this

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

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


  • 5.  RE: Formula to show duration between 2 dates in order to calculate how long employee has been employed

    Posted 07-18-2017 22:07

    That works perfectly, thank you so much.



  • 6.  RE: Formula to show duration between 2 dates in order to calculate how long employee has been employed

    Posted 01-30-2019 23:25
    I've tried this and it worked perfectly, but how do I get this in number of day? Do I need to switch to duration?

    I am doing mine based off of DATE SENT TO LAB & REPORT DATE. Any help would be appreciated. Thanks


  • 7.  RE: Formula to show duration between 2 dates in order to calculate how long employee has been employed

    Posted 01-30-2019 23:42
    Please post your formula