Difference between Two Datetime Fields in Salesforce

In this Salesforce Tutorial, we will learn the formula to Calculate the difference between two datetime fields in Salesforce. Additionally, we will go over how to use Salesforce Lightning and Salesforce Classic to determine the difference between datetime fields.

I got a task at work that required me to calculate the difference between the date and time fields using Salesforce. So, to fulfill this requirement, I define an advance formula for this.

If you want to learn How to get the difference between 2 datetime fields using Salesforce Lightning and Salesforce Classic, you have come to the right place.

The following are the topics that we will cover in this salesforce tutorial:

• Difference between 2 datetimes in Salesforce
• Calculate the difference between two datetime field in Salesforce Lightning
• Calculate the difference between two datetime field in Salesforce Classic

Difference between 2 datetimes in Salesforce

In Salesforce, there are many scenarios where we may need to get the difference between two datetimes fields in Salesforce.

For example, we want to calculate the difference between 5-03-2022 9:53 and 6-03-2022 10:58 and want to display the result as: 25 Hours 05 Minute.

For this, we will find the difference between the day, hours, minutes, and seconds of two datetime values by subtracting one datetime from the other.

But, if we directly subtract the datetime value, we will get the difference of the dates in fractions. So, to calculate the difference we have to use some of the Salesforce functions such as FLOOR, MOD, ROUND, and TEXT.

And, the difference between two datetime fields in Salesforce returns the result in Number and Text format.

We have two different advance formulas for the difference between two datetimes fields in Salesforce. Either, we can get the difference between two datetime fields in hours and minutes or we can get the difference between two datetime fields in day, hours, minutes, and seconds.

If you, want to find the difference in hours and minutes. Here, is the formula:

IF
(
FLOOR((EndDateTimeField-StartDateTimeField)*24)>9,
TEXT(FLOOR((EndDateTimeField-StartDateTimeField)*24)),
"0" & TEXT(FLOOR((EndDateTimeField-StartDateTimeField)*24))
 )
&"."&
IF
(
ROUND(MOD((EndDateTimeField-StartDateTimeField)*1440,60),0)>9,
TEXT(ROUND(MOD((EndDateTimeField-StartDateTimeField)*1440,60),0)),
"0" & TEXT(ROUND(MOD((EndDateTimeField-StartDateTimeField)*1440,60),0))
 )

Calculate Hours:

• In this formula, to get the number of hours we multiply the difference between the two dates in days by 24.
• Then, we use the FLOOR function to round down the result to the closest integer.
• If the number of hours is less than 10, we also use an IF statement to add a 0 to the result so that we get 09 rather than just 9.

Calculate Minutes:

• In this formula, to get the number of minutes we multiply the difference between the two dates in days by 1440. Then, we utilize the MOD function in order to determine the remainder of a division.
• The ROUND function is also used to eliminate decimal points.
• We also use an IF statement to add a 0 to the result so that we get 09 rather than just 9 if the number of minutes is less than 10.

Note: We use the & character to join these two parts together.

---

If you, want to find the difference in day, hours, minutes, and seconds. Here, is the formula:

IF (FLOOR((EndDateField-StartDateField)) > 0, TEXT(FLOOR((EndDateField-StartDateField)) ) & " Days ", "") 

&
IF(FLOOR(MOD((EndDateField-StartDateField)* 24, 24 )) > 0, TEXT(FLOOR(MOD((EndDateField-StartDateField)* 24, 24 ))) & " Hours ","") 
 & 
TEXT(ROUND(MOD((EndDateField-StartDateField)* 24 * 60, 60 ), 0)) & " Minutes "
 & 
TEXT(ROUND(MOD((EndDateField-StartDateField)* 24 * 60*60, 60 ), 0)) & " Seconds"

Calculate Days:

• In this formula, to get the number of days, we calculate the difference between the two datetime fields.
• Then, we use the FLOOR function to convert the fractional result to the whole number of days.
• If the number of days is greater than 0, we use the IF statement to convert the result to the text string using the TEXT function, then we append it with the text Days.

Calculate Hours:

• In this formula, to get the number of minutes we multiply the difference between the two dates in days by 24.
• Then, we use the FLOOR function to round down the result to the closest integer and the MOD function returns the remainder after dividing the difference between the two datetime multiplied by 24 by 24.
• If the number of hours is greater than 0, we use the IF statement to convert the result to the text string using the TEXT function, then we append it with the text Hours.

Calculate Minutes:

• In this formula, to get the number of minutes, we use the FLOOR function to round down the result to the closest integer.
• And the MOD function returns the remainder after dividing the difference between the two datetime multiplied by 24*60 by 60.
• The resultant value is then converted to a text string using the TEXT function and attached with the text Minutes.

Calculate Seconds:

• In this formula, to get the number of seconds, we use the FLOOR function to round down the result to the closest integer.
• And the MOD function returns the remainder after dividing the difference between the two datetime multiplied by 24*60*60 by 60.
• The resultant value is then converted to a text string using the TEXT function and attached with the text Seconds.

In order to create a text string that represents the time difference in the format of Days, Hours, Minutes, and Seconds, all four parts are combined together using the & symbol.

And then any components of the datetime field difference with a value of 0 will not be displayed.

---

With this, we now know the Salesforce formula to calculate the difference between two datetime fields. Now, we will move further and see How to calculate the difference between two datetime fields in Salesforce Lightning.

Read Salesforce Formula to Calculate Days Between Dates

Calculate the difference between two datetime field in Salesforce Lightning

To calculate the difference between two datetime fields in Salesforce Lightning, the following are the steps:

Step 1: Click the Setup icon in the top right corner of the page to access Salesforce Setup.

Step 2: Select Object Manager from the left side of the menu.

Step 3: Look for the object whose field you wish to find the difference between two datetime fields. In this instance, I choose the Lead object from the list of objects.

Step 4: Select Fields & Relationships.

Step 5: Next, select New.

Step 6: Choose the Formula as the field type.

Step 7: Click Next to proceed.

Step 8: After entering a Field Label for the formula, the Field Name will be filled in automatically. Here, I specify the field label as Total Time Period.

Step 9: Select the Formula Return Type as Text. After that, select Next.

Step 10: In the Advance Formula editor, enter the advance formula to determine the difference between two datetime fields. The formula is as follows:

IF (FLOOR((End_Date_Time__c - Start_Date_Time__c)) > 0,
TEXT(FLOOR((End_Date_Time__c - Start_Date_Time__c)) ) & " Days ", "") 
&
IF(FLOOR(MOD((End_Date_Time__c - Start_Date_Time__c)* 24, 24 )) > 0, TEXT(FLOOR(MOD((End_Date_Time__c - Start_Date_Time__c)* 24, 24 ))) & " Hours ","") 
 & 
TEXT(ROUND(MOD((End_Date_Time__c - Start_Date_Time__c)* 24 * 60, 60 ), 0)) & " Minutes "
 & 
TEXT(ROUND(MOD((End_Date_Time__c - Start_Date_Time__c)* 24 * 60*60, 60 ), 0)) & " Seconds"

• The difference between two date/time fields called “End_Date_Time__c” and “Start_Date_Time__c” is calculated using this formula, which provides the result in days, hours, minutes, and seconds.
• First, we check if the number of days is greater than 0 days. It returns the number of days between the two dates followed by the word Days. If not, it returns an empty string.
• Next, we calculate the number of hours between the two dates using the MOD function to get the remainder after dividing the time duration by 24 hours. If this value is greater than 0, it returns the number of hours followed by the word Hours. If not, it returns an empty string.
• After dividing the duration by one hour, the formula then determines the number of minutes that exist between the two dates using the MOD function. This value is rounded to the nearest minute using the ROUND function and returned with the word Minutes after it.
• Finally, after dividing the duration by one minute, the formula determines the number of seconds between the two dates using the MOD function. It provides this value rounded to the closest second, along with the word Seconds.
• The sum of the number of days, hours, minutes, and seconds is concatenated together as the entire result using & operator.

Step 11: To verify that the formula is valid, click Check Syntax.

Step 12: We can specify how you want to handle the empty field and include the Description and Help Text as optional fields.

Step 13: Then, select Next.

Step 14: Choose the appropriate field’s level of security, and then check the box beside visible to make it visible for everyone.

Step 15: Click, Next.

Step 16: Select the Page Layout Name for which the formula field is to be stated.

Step 17: Click Save to save the formula field.

Once we’ve set up the formula field, we may use it. Let’s examine an example:

• Open up the Leads Tab and create an order with Start Date Time and End Date Time and save it.
• After that, click on the Details section, and here the Total Time Period field will appear that shows the difference between the two Date/Time field.

As a result, we now understand how to apply an advanced formula in Salesforce Lightning to determine the difference between two datetime fields. We’ll then examine how to use the advance formula in Salesforce Classic to calculate the difference between two datetime fields.

Read How to Activate Data Integration Rules in Salesforce

Calculate the difference between two datetime field in Salesforce Classic

To calculate the difference between two datetime fields in Salesforce Classic, the following are the steps:

Step 1: Open your Salesforce Classic edition from the Salesforce Lightning. Switch to Salesforce Classic by clicking on Avtar.

Step 2: Select Setup from the menu.

Step 3: Navigate to the Build section by scrolling down. Select the object for which we want to find the difference between two datetime fields under Customize. I select Contract in this case from the list of objects.

Step 4: Select Fields from the dropdown menu by clicking on it.

Step 5: As we continue to scroll down the page, we will get the New button under Custom Fields & Relationships, click on it.

Step 6: Choose the Formula as the field type.

Step 7: To continue, click Next.

Step 8: After entering a Field Label for the formula, the Field Name will be filled in automatically. The Contract Time Period is entered here as Field Label.

Step 9: Click Next, after selecting Text as the Formula Return Type.

Step 10: Fill out the advance formula editor with the formula to get the difference between two datetime fields. The formula reads as follows:

IF
(
FLOOR((Vaild_Upto__c - Activate_Date__c)*24)>9,
TEXT(FLOOR((Vaild_Upto__c - Activate_Date__c)*24)),
"0" & TEXT(FLOOR((Vaild_Upto__c - Activate_Date__c)*24))
 )
&"."&
IF
(
ROUND(MOD((Vaild_Upto__c - Activate_Date__c)*1440,60),0)>9,
TEXT(ROUND(MOD((Vaild_Upto__c - Activate_Date__c)*1440,60),0)),
"0" & TEXT(ROUND(MOD((Vaild_Upto__c - Activate_Date__c)*1440,60),0))
 )

• In this example, the formula calculates the duration between the Activate_Date__c and Valid_Upto__c in hours and minutes.
• Then, we calculate the number of hours by subtracting the Activate_Date__c from Vaild_Upto__c, multiplying the result by 24 to convert it to hours.
• Next, we round down the result using the FLOOR function and convert the result to text format using the TEXT function.
• If the number of hours is less than 10, a zero is added.
• Then, we calculate the number of minutes by subtracting the Activate_Date__c from Vaild_Upto__c, multiplying the result by 1440 to convert it to minutes.
• Then, taking the remainder of dividing the result by 60 using the MOD function, then round the result to zero decimal places using the ROUND function, and convert the result to text format using the TEXT function.
• If the number of minutes is less than 10, a zero is added.
• The final output is combined and displayed in the format hh.mm.

Step 11: Click Check Syntax to make sure the formula is correct.

Step 12: You can define how the empty field should be handled and choose whether to provide the Help Text and Description.

Step 13: Then, click Next.

Step 14: Choose the appropriate Field’s Level Security, and then check the box Visible to make it visible to all.

Step 15: Click, Next.

Step 16: Decide which Page Layout, will receive the new formula field.

Step 17: To save the formula field, click Save.

We can use the formula field once we’ve configured it. Let’s look at an illustration:

• Open the Contract Tab, enter the Activated Date and the Valid Upto, and then save the lead.
• The Contract Time Period field, which displays the difference between the two Datetime field, then appear in the detail section.

In a conclusion, we now understand how to get the difference between two datetime fields using Salesforce Classic’s advanced formula.

You may also like to read:

• Salesforce Formula to get Last Day of Month
• Salesforce Formula DATETIMEVALUE
• Salesforce Weekday Formula
• How to add Picklist Values in Salesforce

Conclusion

We now know how to calculate the difference between two datetime fields using the advanced Salesforce formula.

Furthermore, we discovered the formula to get the difference between DateTime fields using both Salesforce Classic and Salesforce Lightning.

Plus, the following are the topics that we have discussed:

• Difference between 2 datetimes in Salesforce
• Calculate the difference between two datetime field in Salesforce Lightning
• Calculate the difference between two datetime field in Salesforce Classic