In this tutorial, we are going to learn about the FormatDuration function in Salesforce, and we will also discuss a use case of the FormatDuation function using a custom formula field in Salesforce.
Salesforce provides a wide range of Functions that are helpful in Formula fields and Report formulas that are used to get specific records in reports.
What is FormatDuration() Function in Salesforce
In Salesforce, FormatDuration is a DateTime/time function that returns the duration between the two date/time fields. When we use the Formatduration() function in a formula, if the difference between the two date/time fields is 2 days and 30 minutes then we will get the output as 02:00:30:00 in the DD:HH:MM: SS format.
There are a few considerations that we should be aware of while using the FormatDuration() function in Salesforce fields and reports formulas.
- Depending on your use case, you might need to integrate the FormatDuration() function with other functions or expressions.
- In the expression of the Formatduration function, always use the dateTime/time field; any other field will give a Syntax error.
Syntax of the Salesforce Formatduration() Function
The syntax of the Salesforce FormatDuration() function is written in the following way.
FORMATDURATION(numSeconds[, includeDays] | dateTime/time, dateTime/time)
In the above syntax, numSeconds is supposed to be replaced with optional days, or the difference between times or DateTime as HH:MI: SS.
This includeDays parameter is a Boolean value that indicates whether or not to include the days in the formatted duration. If days are included it will be TRUE else it will be False.
The dateTime field in the above syntax is used as an expression, and both datetime fields are supposed to be replaced by a datetime/time field.
The output type of this function is taken as text, and the output format is DD:HH:MM: SS.
Where D = day, H = hours, M = minutes, and S = Seconds.
How to use the FormatDuration function in Salesforce Lightning
In the below steps, we will discuss the execution and use case of the FormatDuration function in Salesforce Lightning. In this example, we will create a custom Formula field using the FormatDuration function.
1. Navigate to the Setup page of Lightning, and for that click on the settings icon, and in the dropdown select the Setup option.
2. On the Setup page of Lightning, click on the Object Manager tab.
3. In the Object Manager setup select an object in which you have to create a formula field using FormatDuration function. In this case, I have selected the object Account.
4. In the selected object click on Fields & Relationships then in the setup of Fields & Relationships, click on the New button.
5. In this step, select the Formula data type and click on the Next button.
6. Enter the Field Label for the output field and the Field Name will be auto-filled according to the entered Field label.
After this select the Text formula return type and click on the Next button.
7. In this step, we will enter the formula using the function FORMATDURATION, and for this, go to the Functions scroll bar on the right-hand side select the function FORMATDURATION, and click on the button Insert Selected Function.
8. To add a field in this formula, click on the Insert Field button that is above the text area.
9. In this step, select a DateTime/Time field and click on the Insert button. In this example, I have selected a Start Date field which is a custom Date/Time field.
If you select any field other than the Date/Time field, it will show a syntax error in the formula.
10. In the formula text area, I have entered the below formula after selecting fields in the above steps.
FORMATDURATION( Start_Date_c , End_Date_c)
In the above formula, I have used the custom dateTime/time fields, Start Date, and End Date, a comma separates these fields (,).
After adding the formula, click on the Check Syntax button to verify the formula, and on successful verification, click on the Next button.
The output format will be DD:HH:MM: SS, where D = day, H = hours, M = minutes, and S = seconds.
11. In the next step, activate the Visible checkbox to make this formula field visible to all users and click on the Next button.
You can also select specific profiles instead of selecting all profiles by activating the checkboxes of specific profiles.
12. In the last step, make sure that the field is activated in the object page layouts, and then click on the Save button.
As we click on the Save button, the formula field with the FormatDuration function will be created in the selected object.
In this example, I have created that formula field in the Account object so, I will open an account record to view whether the FormatDuration function is giving the output or not.
Now in the Account record, I will enter the Start Date and End Date that we used in the formula to get the duration between these two Date/Time fields.
As we can see in the above image, on entering the Date/Time fields, the output of the formula field Time Duration shows the Format Duration between the two fields in the format of DD:HH:MM: SS.
In this way, we have successfully executed the FormatDuration function with the help of a custom formula field in Salesforce Lightning.
How to use the FormatDuration function in Salesforce Classic
In the below steps, we will discuss the execution and use case of the FormatDuration function in Salesforce Classic. In this example, we will create a custom Formula field using the FormatDuration function.
1. Navigate to the Setup page of Salesforce Classic by clicking on the Setup tab on the upper right side of the screen.
2. On the Setup page, click on the Customize dropdown in the left sidebar and select Object-> Fields.
In this example, I have selected the object Opportunites.
3. In the object setup, scroll down to the section Custom Fields & Relationships and click on the New button.
4. In this window, select the Formula field type and click on the Next button.
5. In this step, enter the label for the Output in the Field Label and the Field Name will be auto-filled according to the entered Field Label.
In the Formula Return Type select the Text option and click on the Next button.
6. Now, we will enter the formula in the formula editor, and for that, select the FormatDuration function from the scroll bar on the right-hand side and click on the button Insert Selected Function.
7. Now, we can view the syntax of the FormatDuration function in the formula editor.
On these two dateTime/time fields, we have to select fields, and for that, click on the button Insert field.
8. In this window, select a dateTime/time field and click on the Insert button. In this example, I have selected the dateTime/time fields Created Date and Last Modified Date.
For the FormatDuration function, we can only select a dateTime/time field; otherwise the formula will get a Syntax error.
9. After adding the fields in the formula, click on the button Check Syntax to check whether the formula is correct or not. On approval of the correct syntax, click on the Next button.
10. In this step, we have to set the field-level security for this custom formula field. Here, we will select profiles by activating the checkbox adjacent to profiles to give them access to this formula field.
In this example, I have activated the Visible checkbox to make this field accessible to all profiles in the org.
After selecting the profiles, click on the Next button.
11. In this window, make sure page layouts are activated, so that this formula field will be visible on the page layouts.
At last, click on the Save button.
As we click on the Save button, the custom formula field with the FomatDuration function will be created in the selected object.
In this example, I have created this field with the FormatDuration function in the Opportunities object. On the record page of any opportunity account, I can view this formula field that I have labeled as Opportunity Duration.
In the above image, we can see the custom formula field that we have created using the FormatDuration function is displaying the format duration between the dateTime/time fields that we used in the formula.
In this way, we have successfully executed the FormatDuration function using the custom formula field in Salesforce Classic.
In this tutorial, we have learned about a real-time use case of the FormatDuration() function in Salesforce, and we have successfully created a custom formula field using a FormatDuration() function in Salesforce Lightning and Salesforce Classic.
We also discussed the syntax and uses of the FormationDuration() function, with the considerations that we should be aware of while executing this formula in fields and report formulas.
You may like to read:
- Salesforce DATE Function
- Salesforce ADDMONTHS Function
- Salesforce MINUTE Function
- Salesforce TIMEVALUE Function
- Salesforce NULLVALUE Function
- Salesforce NOT Function
I am Bijay Kumar, the founder of SalesforceFAQs.com. Having over 10 years of experience working in salesforce technologies for clients across the world (Canada, Australia, United States, United Kingdom, New Zealand, etc.). I am a certified salesforce administrator and expert with experience in developing salesforce applications and projects. My goal is to make it easy for people to learn and use salesforce technologies by providing simple and easy-to-understand solutions. Check out the complete profile on About us.