# Discussions

View Only
• #### 1.  Calculate year and month tenure from two dates

Posted 24 days ago
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
------------------------------

• #### 2.  RE: Calculate year and month tenure from two dates

Posted 24 days 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
------------------------------

• #### 3.  RE: Calculate year and month tenure from two dates

Posted 22 days ago
Edited by Maria Pace 22 days ago

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

• #### 4.  RE: Calculate year and month tenure from two dates

Posted 22 days ago
Edited by Mark Shnier (YQC) 22 days 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
------------------------------

• #### 5.  RE: Calculate year and month tenure from two dates

Posted 22 days ago
The duration works but I get it in days and would like it in year and month. So if start date is 11/01/2020 and end date is 12/05/2021 the duration would be 1 year 1 month.

------------------------------
Maria Pace
------------------------------

• #### 6.  RE: Calculate year and month tenure from two dates

Posted 22 days ago
Can you post your current formula with a copy paste (not a screenshot)?

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

• #### 7.  RE: Calculate year and month tenure from two dates

Posted 22 days 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
------------------------------

• #### 8.  RE: Calculate year and month tenure from two dates

Posted 22 days ago
Edited by Mark Shnier (YQC) 22 days 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
------------------------------

• #### 9.  RE: Calculate year and month tenure from two dates

Posted 22 days ago
It works, thank you so much!

------------------------------
Maria Pace
------------------------------