+ Start a Discussion
NevDevNevDev 

Formula field to show difference between two dates in word format

Hi Guys,

I have created a formula field that shows me the difference between 2 date fields in word format (see screen shot below). We have identified an issue with the formula e.g. if  the Start Date = 1st March 2018 and the End Date = 29th April 2018 it records this as only 1 month which is incorrect for our business requirement. It needs to record this as 2 months. I have outlined the formula that I am currently using below along with a screen shot of the fields in question which shows an example of what I am referring too. Is anyone able to help me resolve this?

IF(   Lease_End_Date__c    >= DATE(YEAR( Lease_End_Date__c ), MONTH(   Lease_Start_Date__c   ), DAY( Lease_Start_Date__c )),
TEXT(YEAR( Lease_End_Date__c ) - YEAR( Lease_Start_Date__c )),
TEXT(YEAR( Lease_End_Date__c ) - YEAR( Lease_Start_Date__c ) - 1)) & " Years" &
IF(MONTH( Lease_End_Date__c ) = MONTH( Lease_Start_Date__c),
IF(DAY( Lease_End_Date__c ) >= DAY( Lease_Start_Date__c ), "", " & 11 Months"),
IF(MONTH( Lease_Start_Date__c ) > MONTH( Lease_End_Date__c ),
" & " & TEXT(12 - (MONTH( Lease_Start_Date__c ) - MONTH( Lease_End_Date__c ))) & " Months",
" & " & TEXT(MONTH( Lease_End_Date__c ) - MONTH( Lease_Start_Date__c )) & " Months"))

User-added image
Best Answer chosen by NevDev
Jainam ContractorJainam Contractor
Hi Nev,

Check the below formula and let me know if it works.
 
IF(Lease_End_Date__c >= Lease_Start_Date__c, 
			TEXT(FLOOR((Lease_End_Date__c - Lease_Start_Date__c)/365)) & " Years " & 
			TEXT(FLOOR(MOD((Lease_End_Date__c - Lease_Start_Date__c),365)/31)) & " Months " &
			TEXT(MOD(MOD((Lease_End_Date__c - Lease_Start_Date__c),365),31)) & " Days ", NULL					
		)

Thanks,
Jainam Contractor.

All Answers

Tiago Armando CoelhoTiago Armando Coelho
Hi Nev, 

Please detail a little more your requirement in order so we can help you.

Kind regards, 
Jainam ContractorJainam Contractor
Hi Nev,

It will not show as 2 months as it has not completed 2 months. It can show you the number of days as well if you want to.

Else the logic might be different for all possible scenarios.

Its better to display number of days also alongwith the Years and Months. Or you can add more if condition of the DAYS to display number of Months + 1.

Let me know how you need to display the field.

Thanks,
Jainam Contractor
NevDevNevDev
Hi Jainam,

I think that you are correct with adding the number of days to the formula. Would you be able to help me with that?
Jainam ContractorJainam Contractor
Hi Nev,

Please check the below formula and let me know if it works for you. I tried in my Org and it worked for me.
IF(Lease_End_Date__c >= Lease_Start_Date__c, 
			TEXT(FLOOR((Lease_End_Date__c - Lease_Start_Date__c)/365)) & " Years " & 
			TEXT(FLOOR(MOD((Lease_End_Date__c - Lease_Start_Date__c),365)/30)) & " Months " &
			TEXT(MOD(MOD((Lease_End_Date__c - Lease_Start_Date__c),365),30)) & " Days ", NULL					
		)
I have put a check that the End Date should be greater than the Start Date. You can remove that check if you don't require.

Please let me know if it works for you. Mark this as the solution if it solved your purpose.

Thanks,
Jainam Contractor,
Salesforce Consultant,
Varasi LLC
www.varasi.com

 
NevDevNevDev
Hi Jainam,

I've tried this but it doesn't seem to be working. If you look at the below screen shot the Start Date = 1st March 2018 and the End Date = 29th April 2021. The output should be 3 Years 1 Month 29 Days, but your formula reads 3 Years 2 Months 1 Days
NevDevNevDev
User-added image
Jainam ContractorJainam Contractor
Hi Nev,

Check the below formula and let me know if it works.
 
IF(Lease_End_Date__c >= Lease_Start_Date__c, 
			TEXT(FLOOR((Lease_End_Date__c - Lease_Start_Date__c)/365)) & " Years " & 
			TEXT(FLOOR(MOD((Lease_End_Date__c - Lease_Start_Date__c),365)/31)) & " Months " &
			TEXT(MOD(MOD((Lease_End_Date__c - Lease_Start_Date__c),365),31)) & " Days ", NULL					
		)

Thanks,
Jainam Contractor.
This was selected as the best answer
NevDevNevDev
Hi Jainam,

This seems to be working. I will monitor it and get back to you if I come across any issues. thanks for your help.