Forum Discussion

MariaPace's avatar
MariaPace
Qrew Member
3 years ago

Calculate year and month tenure from two dates

I have a form with start date and end date. How do I calculate tenure in years and months (e.g., 3 years 4 months) based on these two dates?

------------------------------
Maria Pace
------------------------------
Formulas and functions

8 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    3 years ago
    Here is a formula which you can adapt for the age of a person from today.  You can replace the Today() with your field for the [start date].

    var date MyDate = [End 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
    ------------------------------
    • MariaPace's avatar
      MariaPace
      Qrew Member
      3 years ago

      Thanks! I made the changes but get the error "Expecting duration but found text" at $RawResult. Should I change the formula type?

       

      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew Champion
        3 years ago
        OK, let's go back to basics, maybe I have way over complicated this.

        Can you give me an example of the start date and the end date and the response you want from the formula.  

        If you just want the Duration then you just subtract

        [End date] - [Start Date]


        ------------------------------
        Mark Shnier (YQC)
        mark.shnier@gmail.com
        ------------------------------
    • MariaPace's avatar
      MariaPace
      Qrew Member
      3 years ago

      Thanks! I made the changes but get the error "Expecting duration but found text" at $RawResult. Should I change the formula type?



      ------------------------------
      Maria Pace
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew Champion
        3 years ago
        Yes, if the result is to be in words then yes change the formula type to formula text.

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