Try It Out: Define a “Days Open” Custom Formula Field

Step 2 of the New Custom Field wizard appears.

Custom Formula Field Wizard Step 2

4. In the Field Label field, enter Days Open. Field Name is populated automatically.
5. Select the Number formula return type.

In this case, even though we're subtracting Date fields, we want to end up with just a regular numeric value.

6. Change the Decimal Places value to 0, and click Next.

Now it's time to enter the details of our formula.

7. Click the Advanced Formula tab, as shown in the following screenshot.

Custom Formula Field Editor

We want to use the Advanced Formula tab so we can access the platform's built-in functions through the Functions list on the right side.

8. From the Functions list, double-click IF.

Our formula now looks like this:

1IF(logical_test, value_if_true, value_if_false)

Let's go ahead and define the logical test:

9. Delete logical_test from the formula, but leave your cursor there.
10. From the Functions list, double-click ISBLANK.
11. Delete expression from the ISBLANK function you just inserted, but leave your cursor there.
12. Click the Insert Field button. Two columns appear in an overlay.
13. In the left column, select Position.
14. In the right column, select Close Date.
15. Click Insert.

Did you notice that you didn't have to remember to use the API name of the Close Date field? The platform remembered for you when it inserted the value. Our formula now looks like this:

1IF( ISBLANK( Close_Date__c ) , value_if_true, value_if_false)

Now, let's specify the value if our logical test evaluates to true:

16. Delete value_if_true from the formula, but leave your cursor there.
17. Press ENTER on your keyboard, and space over 10 spaces.

Adding the carriage return and spaces makes our formula more legible for others.

18. From the Functions list, double-click TODAY.
19. Click the Insert Operator button and choose Subtract.
20. Click the Insert Field button.
21. In the left column, select Position.
22. In the right column, select Open Date.
23. Click Insert.

We're getting closer—our formula now looks like this:

1IF( ISBLANK( Close_Date__c ) ,
2          TODAY()  -  Open_Date__c , value_if_false)

Finally, let's specify the value if our logical test evaluates to false:

24. Delete value_if_false from the formula, but leave your cursor there.
25. Press ENTER on your keyboard, and space over 10 spaces.
26. Click the Insert Field button.
27. In the left column, select Position.
28. In the right column, select Close Date and click Insert.
29. Click Insert Operator and choose Subtract.
30. Click the Insert Field button.
31. In the left column, select Position.
32. In the right column, select Open Date and click Insert.

Our formula now matches our original:

1IF( ISBLANK( Close_Date__c ) ,
2          TODAY()  -  Open_Date__c ,
3          Close_Date__c  -  Open_Date__c )

Now that we've gone through those steps of the procedure, note that we could have just typed in the formula that we figured out in the last section. However, using the formula editor is a lot easier because you don't have to remember function syntax or API names of fields and objects. Let's keep going and finish up this field:

33. Click Check Syntax to check your formula for errors.
34. Select Treat blank fields as blanks, and click Next.
35. Accept all remaining field-level security and page layout defaults.
36. Click Save.
37. On the Positions Fields page, scroll down to the Positions Custom Fields & Relationships section.
38. Click Edit next to the new Days Open field.
39. In the Description text box, enter The number of days a position has been (or was) open.
40. Add an optional Help Text description if you wish.
41. Click Save.