Forum Discussion

AddieBrunson's avatar
AddieBrunson
Qrew Member
3 years ago

Calculating Employee Tenure

Hello, 

I am needing to calculate employee tenure in years and months. I currently have a date field "Start Date" for the date of hire. Is there a formula to easily calculate this?

Thank you in advance!

------------------------------
Addie Brunson
------------------------------

5 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    3 years ago
    Can you provide an example of result desired?

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
    • AddieBrunson's avatar
      AddieBrunson
      Qrew Member
      3 years ago
      Sure. Assuming I'd be looking at the data today (3/16/22):

      Employee start date is 11/1/21, result would say 4 months. 
      Employee start date is 7/1/19, result would say 2 years 8 months.

      ------------------------------
      Addie Brunson
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew Champion
        3 years ago
        not tested lately but try this.  Just change the first line to use your hire date field.



        var date MyDate = [Employee Start Date];

        var text Years = ToText(Year(Today())-Year($MyDate) -

        If( Month(Today())<Month($MyDate)

        or

        (Month(Today())=Month($MyDate) and Day(Today())<Day($MyDate)),1,0));



        var text Months = If(

        Month(Today()) > Month($MyDate), ToText(Month(Today()) - Month($MyDate)),

        Month(Today()) = Month($MyDate) and Day(Today()) >= Day($MyDate), ToText(Month(Today()) - Month($MyDate)),

        Month(Today()) = Month($MyDate) and Day(Today()) < Day($MyDate), "11",

        ToText(12-(Month($MyDate) - Month(Today()))));



        var text DaysDisplay = If ( Day(Today()) > Day($MyDate), ToText( Day(Today()) - Day($MyDate) ),

        Day(Today()) = Day($MyDate), "0",

        Day(Today()) < Day($MyDate), ToText( Day($MyDate)-Day(Today()) ));




        var text RawResult=

        $Years & " Year" & If ($Years <> "1","s","") & "<br>" &

        $Months & " Month" & If($Months<>"1","s", "") "<br>" &

        $DaysDisplay & " Day" & If($DaysDisplay<>"1","s", "");



        $RawResult

        ------------------------------
        Mark Shnier (YQC)
        mark.shnier@gmail.com
        ------------------------------