Calculating How Long a Position Has Been Open

Let's think about another custom formula field that we could create for our Position object—a custom formula field that calculates how many days a position has been open. To do this, let's first think about the logic that we should use to define the field, and then we can go through the process of creating it in our Recruiting app.

Let's think about the data that we need to make this calculation: we need to know the current date and the date the position was created. If we could somehow subtract these two, we'd have the number of days that the position has been open. Fortunately, it's easy to get both of these values.

For the current date, we can use the platform's built-in TODAY() function. TODAY() returns today's date.
For the date that the position was opened, we can use the Open Date field that we defined in the last chapter.

When using fields in formulas, you can't just refer to a field by its name. Instead, you need to refer to it by its merge field name, also called its API name. The format of the API name is typically the name of the field but with underscores instead of spaces. For custom fields, the API name is suffixed with two underscores and the letter “c,” like this: Open_Date__c. This naming convention in the platform helps distinguish between standard and custom fields.

You don't need to memorize the API names of fields you want to use in formulas. Simply use the field picker in the formula editor to insert fields and the platform automatically inserts the API name for you. If you ever want to know the API name of a specific field and you aren't using the formula editor, you can view the field's detail page.

Tip

Now that we have our two dates, we want to subtract them: TODAY() - Open_Date__c. Even if the two dates span different months or years, the platform is sophisticated enough to know how to handle all the intricacies of such a calculation behind the scenes. We just have to provide the dates, and the platform can do all the rest.

So far so good, but one problem still remains—what if the position has already closed? Our formula only works if we assume the position is still open. Once it closes, however, the value of our current formula will continue to increment every day as TODAY() gets farther and farther away from the original Open Date. If we can, we want to use the Close Date field in the formula instead of TODAY() after a position closes. How can we do this?

Once again, we can dip into the extensive library of platform functions. The IF() function allows us to perform a test and then return different values depending on whether the result of the test is true or false. The IF() function's syntax looks like:

1IF(logical_test,
2           value_if_true,
3           value_if_false)

For the logical_test portion, we'll test whether the Close Date field has a value—if it does, the position obviously must be closed. We'll test for this with a third built-in function: ISBLANK(). ISBLANK() takes a single field and returns true if it does not contain a value and false if it does. So now our formula looks like:

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

By replacing value_if_true and value_if_false with the other formulas we talked about, we've now figured out our whole formula.

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

Great! Our formula calculates the number of days a position has been open, regardless of whether it's currently open or closed. Now, let's go define a field for it on our Position object.

Force.com Platform Fundamentals

Calculating How Long a Position Has Been Open