function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Nevin O'Regan 3Nevin O'Regan 3 

period between two date fields in word format

I'm trying to show the Year, Month, Days in word format between two date fields but I can't seem to get it to work right. Below is the formula that I have used. If I have a Lease Start Date as 01.01.2019 and the Lease End Date as 31.12.2019 the formula field shows as 0 Years, 12 Months, 4 Days. Anyone any idea on how I can correct this?

IF(Lease_End_Date__c >= Lease_Start_Date__c, 
TEXT(FLOOR((Lease_End_Date__c - Lease_Start_Date__c)/365)) & " Year(s) " & 
TEXT(FLOOR(MOD((Lease_End_Date__c - Lease_Start_Date__c),365)/30)) & " Month(s) " & 
TEXT(MOD(MOD((Lease_End_Date__c - Lease_Start_Date__c),365),30)) & " Day(s) ", NULL
)
Alan DePewAlan DePew
You could try the solution from this prior post: 
https://success.salesforce.com/answers?id=90630000000CtgqAAC
Mahboob AljiwalaMahboob Aljiwala
Hi Nevin O'Regan,
Try this formula.

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")

Thanks.
Nevin O'Regan 3Nevin O'Regan 3
Hi guys,

I've found a bug / issue with this formula. Here is what is currently happening with one of the records.

Lease_Start_Date__c  = 01/12/2018
Lease_End_Date__c = 30/11/2021

The output when I use this formula shows as 3 years -1 months 29 days when it actually should be 2 years 11 months 29 days
Mahboob AljiwalaMahboob Aljiwala
Hi Nevin O'Regan 3 

Try this one.

IF(NOT(ISBLANK(Experience1__c)), 

TEXT(FLOOR(((End_Date__c -Start_Date1__c)+(End_Date1__c -Start_Date2__c))/365)) &' Years' & ' ' & 

TEXT(FLOOR(MOD((End_Date__c -Start_Date1__c)+(End_Date1__c -Start_Date2__c),365)/30)) &' Months' & ' ' & 

TEXT(MOD(MOD((End_Date__c -Start_Date1__c)+(End_Date1__c -Start_Date2__c),365),30)) &' Days', 

Experience__c 
)