You need to sign in to do that
Don't have an account?
Nevin 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")
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")
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:
2) The formula to display the period between two date fields in Words (Year(s), Month(s), Day(s).
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)
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
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"))
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:
2) The formula to display the period between two date fields in Words (Year(s), Month(s), Day(s).
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)
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.
We can continue more tests even if the first results above are all accurate.
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"))
and is it correct for you?
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)
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.
2 years 7 months & 360 daysinstead of 2 years 6 months 26 days (my formula).360 days must never been shown to end user.
(an error with a big number of days, the "margin" of error for your formula can be about ... one year in some cases)
Lease_Start_Date__c = 10/10/2018
Lease_End_Date__c = 08/11/2021
Period_In_Words__c = 3 Years 1 Month 363 Days.
Are you saying to create formula field Last_Day_Next_Month__c and paste it into formula No.2?
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")
)
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).
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.
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