I'm trying to display the difference between 2 dates and display the results in years and months. I created a calculated field but I keep getting an error. Can someone help me figure this out?
Solved! Go to Solution.
The formula would like like this:
round(dateDiffDays(Sdate,Edate)/365)+" "+"Years"+" "+round(dateDiffDays(Sdate,Edate)/30)%12+" "+"Months"
I have attached a sample Responsive form which demonstrates this formula for your testing.
Months are calculated on a 30 days so not 100% accurate but pretty close. You could always change it to 31 days.
So I just checked your formula and it does not appear to be calculating correctly. For example if the start and end dates are 01/01/2018 – 07/10/2019, that should show 1 Year 7 Months but your formula will show 2 Years 7 Months.
Hmmm I shouldn't have used Round() for the years as it has rounded up. I just wanted to get rid of the decimal places after dividing. I changed the formula so that it just replaces the decimal with blank space and now it outputs correctly based on your dates. (test form attached)
replace((dateDiffDays(Sdate,Edate)/365),"([^0-9].[0-9]*)","")+" "+"Years"+" "+round(dateDiffDays(Sdate,Edate)/31)%12+" "+"Months"
Q. Can you explain the %12? I assume that its dividing but why use the % vs / ?
A. The % is a math function called Modulo. It finds the remainder of a division.
As we have already taken care of the years in the first bit of the formula (divide by 365) we just want to know the remaining months.
Example. If we have 18 months and we divide by 12 we get 1.5.
18/12 will give us 1.5
18%12 will give us 6 (one lot of 12 with 6 remainder)
This may not be perfect and I am sure a more complex formula could get exact but this will take you close.