You need to sign in to do that
Don't have an account?
NevDev
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"))
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"))
Check the below formula and let me know if it works.
Thanks,
Jainam Contractor.
All Answers
Please detail a little more your requirement in order so we can help you.
Kind regards,
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
I think that you are correct with adding the number of days to the formula. Would you be able to help me with that?
Please check the below formula and let me know if it works for you. I tried in my Org and it worked for me. 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
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
Check the below formula and let me know if it works.
Thanks,
Jainam Contractor.
This seems to be working. I will monitor it and get back to you if I come across any issues. thanks for your help.