function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Nevin O'Regan 3Nevin O'Regan 3 

PERIOD IN WORDS BETWEEN TWO DATES

Hi All,

I'm trying to source a formula that works correctly. I need to display the period between two date fields in Words (Year(s), Month(s), Day(s).
I have found a number of suggessted formulas here and also I have been offered solutions here but they don't seem to work with all date periods. For example, I have a Lease Start Date 01.12.2018 and a Lease End Date 30.11.2021. I was using a formula which was suggested to me in the past but it returns a value of 3 Years, - 1 Month, 29 Days when it should be 2 Years 11 Months 29 Days.
The formula that I have used is below. Maybe it needs some kind of adjustment, I don't really know. 

IF(ISBLANK(End_Date__c),

TEXT(Year(  TODAY()  )- Year(  Start_Date1__c  )) &" "&"Years"&" "&
TEXT(Month(  TODAY()  )-Month(  Start_Date1__c  )) &" "&"Months"&" "&
TEXT(Day(  TODAY()  )-Day(  Start_Date1__c  ))&" "&"Days",

TEXT(Year(  End_Date__c  )- Year(  Start_Date1__c  )) &" "&"Years"&" "&
TEXT(Month(  End_Date__c  )-Month(  Start_Date1__c  )) &" "&"Months"&" "&
TEXT(Day(  End_Date__c  )-Day(  Start_Date1__c  ))&" "&"Days")
 
Best Answer chosen by Nevin O'Regan 3
Alain CabonAlain Cabon
Hi Nevin,

This problem is not so easy in fact and many solutions on the internet are not accurate (approximations with modulos 365 and 30). 

I "solved" the problem with two formulas and the recoding of the javascript engine used here:   http://www.calculconversion.com/calculateur-date.html

This javascript engine widely used is here:  https://gist.github.com/adamphillips/612587

It is a quick javascript dateDiff function that returns results in terms of days, months and years taking into account variable month lengths and leap years.

I used two formulas but that is not mandatory.

1)  last_day_next_month__c:  
IF(MONTH(date1__c)=12,DATE(YEAR(date1__c)+1,1,1)-1, DATE(YEAR(date1__c),MONTH(date1__c) + 1,1) -1)

2)  The formula to display the period between two date fields in Words (Year(s), Month(s), Day(s).
IF ( day(date2__c) - day(date1__c) < 0, 
IF( month(date2__c) - month(date1__c) -1 < 0, 
"d1: " & TEXT( YEAR(date2__c) - YEAR(date1__c) -1) & " years " & 
TEXT( MONTH(date2__c) - MONTH(date1__c) +11) & " months " & 
TEXT( DAY(date2__c) - DAY(date1__c) + DAY( last_day_next_month__c )) & " days", 

"d2: " & TEXT( YEAR(date2__c) - YEAR(date1__c)) & " years " & 
TEXT( MONTH(date2__c) - MONTH(date1__c) -1) & " months " & 
TEXT ( DAY(date2__c) - DAY(date1__c) + DAY( last_day_next_month__c )) & " days"), 

IF( month(date2__c) - month(date1__c) < 0, 
"d3: "& TEXT( YEAR(date2__c) - YEAR(date1__c) -1) & " years " & 
TEXT( MONTH(date2__c) - MONTH(date1__c) +12) & " months " & 
TEXT( DAY(date2__c) - DAY(date1__c)) & " days", 

"d4 :" & TEXT( YEAR(date2__c) - YEAR(date1__c)) & " years " & 
TEXT( MONTH(date2__c) - MONTH(date1__c)) & " months " & 
TEXT ( DAY(date2__c) - DAY(date1__c)) & " days") 
)

You can copy paste the defintion of last_day_next_month__c into the formula above.

2018-10-10 - 2021-10-10  =>  3 Y  - 0 M  - 0 D  (d4)

2018-10-10 - 2021-05-05 =>   2 - 6 - 26  (d1)
2018-05-10 - 2021-10-05  => 3 - 4 - 26  (d2)
2018-10-05 - 2021-05-10  => 2 - 7 - 5   (d3)
2018-05-05 - 2021-10-10  => 3 - 5 - 5   (d4)

User-added image

2018-12-01 - 2021-11-30 => 2 - 11 - 29 (d3)

 Start Date 01.12.2018 and a Lease End Date 30.11.2021 = 2 Years 11 Months 29 Days.

We can continue more tests even the first results above are all accurate.
 

All Answers

Nevin O'Regan 3Nevin O'Regan 3
I think I have figured it out, I just need to add the days into the below formula. Could anyone help me with this?

IF( Lease_End_Date_3__c >= DATE(YEAR( Lease_End_Date_3__c ), MONTH( Lease_Start_Date__c ), DAY( Lease_Start_Date__c )),
TEXT(YEAR( Lease_End_Date_3__c ) - YEAR( Lease_Start_Date__c )),
TEXT(YEAR( Lease_End_Date_3__c ) - YEAR( Lease_Start_Date__c )-1)) &" Years"&
IF(MONTH( Lease_End_Date_3__c ) = MONTH( Lease_Start_Date__c),
IF(DAY( Lease_End_Date_3__c ) >= DAY( Lease_Start_Date__c ), "", " & 12 Months"),
IF(MONTH( Lease_Start_Date__c ) > MONTH( Lease_End_Date_3__c ),
" & " & TEXT(12 - (MONTH( Lease_Start_Date__c ) - MONTH( Lease_End_Date_3__c ))) & " Months",
" & " & TEXT(MONTH( Lease_End_Date_3__c ) - MONTH( Lease_Start_Date__c )) & " Months"))
Alain CabonAlain Cabon
Hi Nevin,

This problem is not so easy in fact and many solutions on the internet are not accurate (approximations with modulos 365 and 30). 

I "solved" the problem with two formulas and the recoding of the javascript engine used here:   http://www.calculconversion.com/calculateur-date.html

This javascript engine widely used is here:  https://gist.github.com/adamphillips/612587

It is a quick javascript dateDiff function that returns results in terms of days, months and years taking into account variable month lengths and leap years.

I used two formulas but that is not mandatory.

1)  last_day_next_month__c:  
IF(MONTH(date1__c)=12,DATE(YEAR(date1__c)+1,1,1)-1, DATE(YEAR(date1__c),MONTH(date1__c) + 1,1) -1)

2)  The formula to display the period between two date fields in Words (Year(s), Month(s), Day(s).
IF ( day(date2__c) - day(date1__c) < 0, 
IF( month(date2__c) - month(date1__c) -1 < 0, 
"d1: " & TEXT( YEAR(date2__c) - YEAR(date1__c) -1) & " years " & 
TEXT( MONTH(date2__c) - MONTH(date1__c) +11) & " months " & 
TEXT( DAY(date2__c) - DAY(date1__c) + DAY( last_day_next_month__c )) & " days", 

"d2: " & TEXT( YEAR(date2__c) - YEAR(date1__c)) & " years " & 
TEXT( MONTH(date2__c) - MONTH(date1__c) -1) & " months " & 
TEXT ( DAY(date2__c) - DAY(date1__c) + DAY( last_day_next_month__c )) & " days"), 

IF( month(date2__c) - month(date1__c) < 0, 
"d3: "& TEXT( YEAR(date2__c) - YEAR(date1__c) -1) & " years " & 
TEXT( MONTH(date2__c) - MONTH(date1__c) +12) & " months " & 
TEXT( DAY(date2__c) - DAY(date1__c)) & " days", 

"d4 :" & TEXT( YEAR(date2__c) - YEAR(date1__c)) & " years " & 
TEXT( MONTH(date2__c) - MONTH(date1__c)) & " months " & 
TEXT ( DAY(date2__c) - DAY(date1__c)) & " days") 
)

You can copy paste the defintion of last_day_next_month__c into the formula above.

2018-10-10 - 2021-10-10  =>  3 Y  - 0 M  - 0 D  (d4)

2018-10-10 - 2021-05-05 =>   2 - 6 - 26  (d1)
2018-05-10 - 2021-10-05  => 3 - 4 - 26  (d2)
2018-10-05 - 2021-05-10  => 2 - 7 - 5   (d3)
2018-05-05 - 2021-10-10  => 3 - 5 - 5   (d4)

User-added image

2018-12-01 - 2021-11-30 => 2 - 11 - 29 (d3)

 Start Date 01.12.2018 and a Lease End Date 30.11.2021 = 2 Years 11 Months 29 Days.

We can continue more tests even the first results above are all accurate.
 
This was selected as the best answer
Alain CabonAlain Cabon
In english: http://www.calculconversion.com/age-calculator.html

We can continue more tests even if the first results above are all accurate.
Nevin O'Regan 3Nevin O'Regan 3
Hi Alain,

Thank you for for replying. I have used the below formula which seems to be working. 

IF( Lease_End_Date_3__c >= DATE(YEAR( Lease_End_Date_3__c ), MONTH( Lease_Start_Date__c ), DAY( Lease_Start_Date__c )),
TEXT(YEAR( Lease_End_Date_3__c ) - YEAR( Lease_Start_Date__c )),
TEXT(YEAR( Lease_End_Date_3__c ) - YEAR( Lease_Start_Date__c )-1)) & " Years" &
IF( Lease_End_Date_3__c >= DATE(YEAR( Lease_End_Date_3__c ), MONTH( Lease_Start_Date__c ), DAY( Lease_Start_Date__c )),
TEXT(MONTH( Lease_End_Date_3__c ) - MONTH( Lease_Start_Date__c )),
TEXT(MONTH( Lease_End_Date_3__c ) - MONTH( Lease_Start_Date__c )+12)) & " Months" &
IF(DAY( Lease_End_Date_3__c ) = DAY( Lease_Start_Date__c),
IF(DAY( Lease_End_Date_3__c ) >= DAY( Lease_Start_Date__c ), "", " & 365 Days"),
IF(DAY( Lease_Start_Date__c ) > DAY( Lease_End_Date_3__c ),
" & " & TEXT(365 - (DAY( Lease_Start_Date__c ) - DAY( Lease_End_Date_3__c ))) & " Days",
" & " & TEXT(DAY( Lease_End_Date_3__c ) - DAY( Lease_Start_Date__c )) & " Days"))
Alain CabonAlain Cabon
What is your result with: 10/10/2018 and 05/05/2021

and is it correct for you?

User-added image

In english: http://www.calculconversion.com/age-calculator.html

(site for testing financial formulas, must be very (not to say totally) accurate because it is for banking systems and we cannot afford to only one cent of difference)
 
Nevin O'Regan 3Nevin O'Regan 3
Hi Alain,

Ye I can imagine that for banking it would need some extra work. I wouldn't have a scenario whereby the dates would reflect the above. We would have Lease periods and these would be blocks between 1 - 5 years. We do offer grace periods of 30, 60 and 90 days which would also reflect on the Lease End Date but that would be as complex as it goes.
Alain CabonAlain Cabon
Ok but your formula is not accurate at all for 10/10/2018 and 05/05/2021 for instance.

2 years 7 months & 360 days instead of 2 years 6 months 26 days (my formula).

360 days must never been shown to end user.
Alain CabonAlain Cabon
The formula that you used is totally wrong for the all dates where month and day of date2 are inferior together to the month and day of date1.
(an error with a big number of days, the "margin" of error for your formula can be about ... one year in some cases)
Nevin O'Regan 3Nevin O'Regan 3
Ye I think your right. I've updated the two date fields to the following values 
Lease_Start_Date__c  = 10/10/2018
Lease_End_Date__c = 08/11/2021

Period_In_Words__c = 3 Years 1 Month 363 Days.
Nevin O'Regan 3Nevin O'Regan 3
I'm going back over your solution. Did you create 2 formula fields? Last_Day_Next_Month__c and the 2nd formula field?

Are you saying to create formula field Last_Day_Next_Month__c and paste it into formula No.2?
Nevin O'Regan 3Nevin O'Regan 3
I think I might be applying this solution incorrectly. I'm not able to get it to work to show the expected output.
Nevin O'Regan 3Nevin O'Regan 3
User-added image

Last_Day_Next_Month__c 

IF(MONTH( Lease_End_Date_3__c )=12,DATE(YEAR(Lease_End_Date_3__c)+1,1,1)-1, DATE(YEAR(Lease_End_Date_3__c),MONTH(Lease_End_Date_3__c) + 1,1))


Lease_Period_In_Words__c

IF ( day( Lease_End_Date_3__c ) - day( Lease_Start_Date__c ) < 0, 
IF( month(Lease_End_Date_3__c) - month(Lease_Start_Date__c) -1 < 0, 
"d1: " & TEXT( YEAR(Lease_End_Date_3__c) - YEAR(Lease_Start_Date__c) -1) & " years " & 
TEXT( MONTH(Lease_End_Date_3__c) - MONTH(Lease_Start_Date__c) +11) & " months " & 
TEXT( DAY(Lease_End_Date_3__c) - DAY(Lease_Start_Date__c) + DAY( Last_Day_Next_Month__c )) & " days", 

"d2: " & TEXT( YEAR(Lease_End_Date_3__c) - YEAR(Lease_Start_Date__c)) & " years " & 
TEXT( MONTH(Lease_End_Date_3__c) - MONTH(Lease_Start_Date__c) -1) & " months " & 
TEXT ( DAY(Lease_End_Date_3__c) - DAY(Lease_Start_Date__c) + DAY( Last_Day_Next_Month__c )) & " days"), 

IF( month(Lease_End_Date_3__c) - month(Lease_Start_Date__c) < 0, 
"d3: "& TEXT( YEAR(Lease_End_Date_3__c) - YEAR(Lease_Start_Date__c) -1) & " years " & 
TEXT( MONTH(Lease_End_Date_3__c) - MONTH(Lease_Start_Date__c) +12) & " months " & 
TEXT( DAY(Lease_End_Date_3__c) - DAY(Lease_Start_Date__c)) & " days", 

"d4 :" & TEXT( YEAR(Lease_End_Date_3__c) - YEAR(Lease_Start_Date__c)) & " years " & 
TEXT( MONTH(Lease_End_Date_3__c) - MONTH(Lease_Start_Date__c)) & " months " & 
TEXT ( DAY(Lease_End_Date_3__c) - DAY(Lease_Start_Date__c)) & " days") 
)
Alain CabonAlain Cabon
Last_Day_Next_Month__c is more exactly: the last day using the first day of the next month less one day.

To get the last day of a month, the trick is to create the a date with the first date of the next month and to substract one day.
This trick is always accurate because you can always add or substract days without checking if it is a leap year (done by the engine of formula of Salesforce).

For example: 5/5/2018 => 1/6/2018 - 1 day = 31/5/2018 = last day of May.
This formula for the last day of the month always uses date1 ( date2 > date1 )

IF(MONTH(date1__c)=12,DATE(YEAR(date1__c)+1,1,1)-1, DATE(YEAR(date1__c),MONTH(date1__c) + 1,1) -1)

For my formula, date2 must always by superior or equals to date1 ( date2 >= date1 ).

Lease_Start_Date__c  = 10/10/2018 = date1 
Lease_End_Date__c   = 08/11/2021 = date2

IF(MONTH(Lease_Start_Date__c  )=12,DATE(YEAR(Lease_Start_Date__c  )+1,1,1)-1, DATE(YEAR(Lease_Start_Date__c  ),MONTH(Lease_Start_Date__c  ) + 1,1) -1)

... returns the last date of the month of the Lease_Start_Date__c.

Period_In_Words__c = 3 Years 1 Month 363 Days.  => [0-n] Years [0-11] Months [0-30] Days, you cannot have more than 30 remaining days (363 is equivalent to many months and almost a complete year).  

The big complication if for the leap years when the last day of February is 29 (instead of 28).
There is a complicated formula only this last day of February when the leap years occur excepted if you use the trick above ( first day of the next month minus one day managed by the formula engine of Salesforce).
 
Nevin O'Regan 3Nevin O'Regan 3
Wow, this works perfectly. Is there anyway of removing the d1: d2: etc. from the beginning of the output?
Alain CabonAlain Cabon
d1: d2; are used only for the debugging because there are four cases to test.
Alain CabonAlain Cabon
Ok good if that helps for your problem.

You have used at first a formula that is very common on the internet but absolutely not accurate for precise cases like many formulas with modulos 365.25 and 30 that claim to be more accurate (Excel without using DATEDIFF). 

Mathematically, it is just a problem of modulos (remaining values of a division by 365.25 and 30), by rounding to the minimum (FLOOR) but that is not sufficient with the real world dates (leap years) and the big part of the mathematical complication is managed by the formula engine of Salesforce directly and .. surely if you just need to add or substract days at last.
Nevin O'Regan 3Nevin O'Regan 3
Thanks a lot for you help with this. I'm not sure that I would have ever figured it out. 
chakrapani reddy 1chakrapani reddy 1
Hi,
I have same requirement i have tested multiple scenarios but date not accurate in below dates.
d1:
8/8/2017
1/3/2019
d1:
8/8/2017
5/4/2019
chakrapani reddy 1chakrapani reddy 1
Please anybody help me on this THANKS IN ADVANCE