In Summer ’14 the Activities team released one of the most anticipated formula enhancements since cross-object Owner fields: the ability to reference the Task Due Date and Event Start Date and End Date in formulas in Salesforce, as well as in workflows and validation rules. This enhancement means that all kinds of things that used to require a trigger are now possible without writing code.
The magic lies in the following fields:
- Date (ActivityDate): the Due Date fields on Tasks, and the Start Date on all-day Events.
- Time (ActivityDateTime): the Start Date (and time) on Events with times.
- Duration (DurationInMinutes): the duration of an Event, in minutes. Use this with ActivityDate or ActivityDateTime to calculate the end time.
Let’s take a look at a few examples of how to apply these in everyday business problems.
How Many Days is a Task Overdue?
Two frequently asked questions in the Answers Community are:
- “How can I add a checkbox field to show when a Task is overdue?”
- “How can I add a field that shows how many days a task is overdue?”
These are problems that just became very easy to solve with formula fields!
To create a checkbox showing whether a task is overdue, go to Customize | Activities | Activity Custom Fields in Setup. Create a custom Formula field with a Checkbox return type. In the formula editor, enter the following formula:
Want an actual count of how many days the task is overdue? Use a Number return type instead and return the difference between the Due Date and the current date:
To make the overdue status really stand out, let’s take it one step further. We can show different colored flags depending on how overdue the task is.
- Green means not overdue
- Yellow means 1 – 7 days overdue
- Red means more than a week overdue
Use a Text formula field return type, and the following formula:
Note that the text in the second argument in the IMAGE() function describes what the flag means, and not that it’s a red or yellow or green flag. This is called “alternate text.” It’s read aloud by screen reader software for users who can’t see the image, and displayed on the page if the image file doesn’t load for some reason. It’s important to use meaningful text for this!
You can now add the formula field to your Task page layout and use it in reports and list views.
How Can I Filter Events on the Day of the Week?
The ActivityDate field is also available on the Event object, but there’s a trick: if it’s an all-day event, use ActivityDate, but if it has times, use the ActivityDateTime field. You must check IsAllDayEvent to determine which value to use.
Here’s another one from the Answers Community: how to filter a list view based on what day of the week the event Start Date falls. Create a formula field with a Text return type, and the following formula:
The key piece of this formula is in the IF() function on line 2:
This checks whether the record is an all-day event, and if it is, it returns ActivityDate. If it’s not an all-day event then because ActivityDateTime is a Date/Time field, the value has to be converted to a Date. The formula then subtracts January 7th, 1900 (a Sunday) from the Start Date to get the number of days between the two dates. It uses the MOD() function to get the remainder of that result divided by 7. This returns 0 if the Start Date was a Sunday, 1 for Monday, and so on up to 6 for Saturday. The CASE() function then returns the string value for that day of the week, which can be be used in other formulas, displayed in page layouts and list views, and so on.
How Can I Prevent Users from Backdating Events by More Than 2 Days?
Do you have users who don’t enter their tasks or events in a timely manner? Use a validation rule to prevent them from creating activities with a Start or End Date in the past.
For Events, go to Customize | Activities | Event Validation Rules in Setup and use the following formula:
The formula checks for two criteria; if either is true, the validation rule is triggered:
- This is a new record, or it’s an existing record and the Start Date value was changed (lines 3-7). The Start Date is more than two days in the past (line 8).
- This is a new record, or it’s an existing record and the Duration value was changed (lines 11-14). Adding the Start Date and the Duration (which must be divided by 1440 to convert minutes to days) results in an End Date that is more than two days in the past (line 15).
Using ISCHANGED() with the Start Date and Duration fields allows changes to be made on other fields on existing Event records – such as changing the Description – without unnecessarily preventing the user from saving them.
How Can I Prevent the Due Date From Being Changed if the Task is Completed?
Validation rules for Task Due Date are simpler than for Events, as they don’t require the checks for IsAllDayEvent, Duration and ActivityDateTime. To create validation rules for Tasks, go to Customize | Activities | Task Validation Rules in Setup.
Here’s an example of a validation formula that prevents the Due Date from being changed if the Task has a specific status, such as “Completed:”
If you have multiple possible “closed” statuses for a task, you can use the IsClosed field instead of checking for multiple picklist values:
In these cases, you don’t want to prevent users from saving a new record with the completed/closed state, so the ISNEW() check isn’t required.
Depending on how firm you need to be, you may also want to prevent users from changing the Status after the task is closed, so they can’t just change it, save the record, and then edit the Due Date.
How Can I Automatically Close a Task if the Due Date is Today?
The Task Due Date (and Event Start Date) are also available in workflow rules. You can use a workflow rule with a field update workflow action to set a field value when the record is saved. This helps encourage good data and enforce business processes. For example, you can set the Status field to “Completed” if the Due Date is the same day the record is created.
To create a workflow rule, go to Create | Workflow & Approvals | Workflow Rules. Click New Rule, and choose the Task object.
There are several settings that control whether workflow rules are executed. In this example, the Status should be automatically updated only when the Task is created, so “Evaluate the rule when a record is: created” In the Rule Criteria section, choose “Run this rule if the following formula evaluates to true” and use the following formula:
Then create a Field Update workflow action that sets the Task Status to Completed.
Formulas in Salesforce: What to do Next
You can use the ActivityDate and ActivityDateTime fields the most of the same places you’d use any other Date or Date/Time field in Salesforce! The one exception is that you can’t set the Due Date or Start Date field value using a workflow field update, though you can reference them in a field update formula to set another field’s value.
If you’re looking for some help with formulas that use dates, there’s a whole tip sheet in the Salesforce Help on Using Date and Date/Time Fields in Formulas, with examples. Just remember to check IsAllDayEvent and use the correct field (ActivityDate or ActivityDateTime) when you’re working with Events.
If you’re looking for help with validation rules, the Salesforce Help also has a page of Sample Date Validation Rules.
Finally, if you’re struggling with a particular formula, there are people willing to help in the Salesforce Developer Community forums and also the Salesforce Success Community.
I hope this blog post got you as fired up about this feature as I am! A big thanks to Activities PM Kayvaan Ghassemieh and his team for making it happen!