Forum Discussion

ToddMolino's avatar
ToddMolino
Qrew Cadet
3 years ago

Calculating Employee Tenure

I have a report that displays each company employee and their tenure based on a hire date. I'd like to amend my formula to show the tenure calculated as of Dec 31st of the prior year versus it being calculated up to the current day of the year. 

Current Formula: 

var number Years = Floor(ToDays(Today() - [Original Date of Assignment])/365);
var number Remainder =Rem(ToDays(Today() - [Original Date of Assignment]),365);
var number Months = Floor($Remainder/30.44);
var number CalcDays = Ceil(Rem($Remainder,30.44));
If([Active],

ToText($Years) & " Years" & " " & $Months & " Months" & " " & $CalcDays & " Days",
If([Role] = "Private Market Agent", "PMA", ""))

The "Tenure" displays as such in their record: 


Thanks for any help with this! 

Todd

------------------------------
Todd Molino
------------------------------
Formulas and functions
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago
    try this

    var date LastDayOfPreviousYear = AdjustYear(LastDayOfYear(Today()),-1);

    var number Years = Floor(ToDays($LastDayOfPreviousYear - [Original Date of Assignment])/365);
    var number Remainder =Rem(ToDays($LastDayOfPreviousYear - [Original Date of Assignment]),365);
    var number Months = Floor($Remainder/30.44);
    var number CalcDays = Ceil(Rem($Remainder,30.44));
    If([Active],

    ToText($Years) & " Years" & " " & $Months & " Months" & " " & $CalcDays & " Days",
    If([Role] = "Private Market Agent", "PMA", ""))


    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
    • ToddMolino's avatar
      ToddMolino
      Qrew Cadet
      3 years ago
      Works like a charm :)

      Much appreciated Mark!

      ------------------------------
      Todd Molino
      ------------------------------