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

MarkShnier__You

Qrew Legend

4 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

Qrew Member

4 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
Qrew Legend
4 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
------------------------------
- MariaPace
  Qrew Member
  4 years ago
  It works, thank you so much!
  
  ------------------------------
  Maria Pace
  ------------------------------

Forum Discussion

Calculate year and month tenure from two dates