Introduction

Formula Fields in Salesforce allow us to create fields with the following Return Types 🡪 Date, Date/Time, and Time!

Date – These fields are great for showing the age of a case, the birthday of a Contact, or when an Opportunity was closed. A Date field includes a year, month, and day. When we subtract two Date values, we’re left with a whole number ( the number of days between the two Dates).

Date /Time – These fields are quite similar to a Date field, except you have a time component in addition to the date. This field is perfect if you want to store the date and time an Account or Opportunity was created or closed. This field stores a year, month, date, and time. The Date/Time value has its precision in seconds. When we subtract two Date/Time values, the resulting value is a decimal number. For example, if the difference is 5.53, there are 5 days, 12 hours (.5 of a day), and 43 minutes (.03 of a day) between the two dates.

Time – A Time field is just like the Date/Time value but without the date. It saves hrs, mins, secs, and milliseconds (HH:MM:SS.MS), and its precision is in milliseconds. For Example, if we subtract Field A = 8:00 pm from Field B = 9:00 pm, the result is equal to 3600000 (which is in milliseconds, and is equal to 60 minutes, i.e., 1 hour). The Time is stored as GMT (Greenwich Mean Time). But to the User viewing the field, it appears in the user’s time zone.

CROSS – OBJECT FORMULAS 

A cross-object formulae displays data from one object on another object. For example, we can display the Last Modified Date (a Date/Time field) of an Account on the page of its associated Contacts using this formula:

Account.LastModifiedDate

Users of your org can see a formula field that references a cross-object field irrespective of the fact that they do not have the authorization to access the object in this field.

Common Date, Date/Time, and Time Functions and Operators

Let’s have a look at some of the operators and functions provided by Salesforce that makes working with these fields a whole lot easier!

Use the Date, Date/Time, and Time Types in Formulas

1. To Find the number of days between two dates

Let’s start with the basics. Let’s create a simple formula field that displays the number of days between today’s date and the date an Opportunity was closed. When we subtract one date from another we get the difference in days as a whole number.

1. From Setup, open Object Manager and select Opportunity.

2. From the sidebar, open Fields & Relationships and click New.

3. Select Formula for Field Type. Click Next.

4. In Field Label, enter Days Since Close. And then select Number for Formula Return Type, and then click Next.

5. Enter the formula given below, then click Next, Next and Save.

TODAY() -  CloseDate 

     Note:

• Ensure that you subtract only past dates from future dates. The reverse will ensue in a negative value.
• The Date and Date/Time fields are not compatible. If we wanted to subtract TODAY() from Created Date ( Date/Time value), we would have to convert it to a date value using the function DATEVALUE() to avoid syntax errors. 

2. Add Days to a Date

Let’s add a field to the Opportunity Details page that will display the date 5 days from today.

1. From Setup, open Object Manager and select Opportunity.

2. From the sidebar, open Fields & Relationships and click New.

3. Select Formula for Field Type. Click Next.

4. In Field Label, enter Five Days from Today. And then select Date for Formula Return Type.

5. Click Next.

6. Enter the formula given below, then click Next, Next and Save.

TODAY() + 5

If we replaced the above formula with:           

TODAY() + 5.5

We would be left with the same result as Salesforce ignoring the numbers after the decimal point. 

3. Add 2 Business Days to Today.

Let’s create a formula field that displays the date of the business day 2 days from today.

• The function WEEKDAY(TODAY()) returns the day of the week for the given date, using 1 for Sunday, 2 for Monday, through 7 for Saturday.
• The function CASE(expression, value1, result1, value2, result2,…,else_result) compares expression to value1, and if there are equivalent, returns result1. If they aren’t, compare value2, and so on. And if none of the values matches, it returns else_result.

We’ll be using the CASE() function instead of the IF() function. The CASE() function is useful when our conditional statements have multiple outcomes. Employing the CASE() function would result in a formula that’s easier to write and understand than a nested IF() function. The cases in the formula are as follows:

• If TODAY() is either a Sunday, Monday or Tuesday or Wednesday, adding 2 business days is equivalent to TODAY() + 2.
• If TODAY() is a Thursday or Friday, adding 2 business days is equivalent to TODAY() + 4 ( 2 business days and 2 weekend days).
• If TODAY() is a Saturday, adding 2 business days is equivalent to TODAY() + 3 ( 2 business days and 1 weekend day).

Our final formula would look this:

CASE( WEEKDAY( TODAY() ), 
      5, TODAY() + 4, 
      6, TODAY() + 4, 
      7, TODAY() + 3, 
      TODAY() +2 
     )

Date, Date/Time, and Time Examples

1. Calculate Business Hours

Let’s create a field that displays the number of hours a business is open.

(BusinessCloseTime - BusinessOpenTime) / 3600000

Since Time values have a precision of milliseconds, to find the number of hours, we need to divide the difference by 3600000 (1000 * 60 * 60).

2. The shift is at least 5 hours

Shift_Ends_Time__c < (Shift_Start_Time__c + 18000000) 

Let’s create a validation rule that ensures that the shift completed by any employee is at least 5 hours. If the shift is lesser than 5 hours, the error message is triggered!

3. Find the Last Day of the Month

Let’s create a field that displays the last day of a month by subtracting 1 from the first day of the following month.

The function ADDMONTHS (date, num) returns the date that is indicated before or after a specified date. If the specified date is the last day of the month, the result date is the last day of the resulting month. For example, if date = 7^th February, 2022 and num=2, the function returns 7^th April, 2022. If date = 28^th February, 2018, the result = 30^th April 2018.

DATE(YEAR(ADDMONTHS(Date__c,1)), MONTH(ADDMONTHS(Date__c,1)), 1) - 1

Let’s understand how this formula works with the help of an example:

• If Date = 2^nd Feb, 2022
• ADDMONTHS(2^nd Feb, 2022, 1 ) = 2^nd March, 2022
• YEAR(ADDMONTHS(2^nd Feb, 2022, 1 ) ) = 2022
• MONTH (ADDMONTHS(2^nd Feb, 2022, 1 ) ) = 3
• DATE(YEAR(ADDMONTHS(Date__c,1)), MONTH(ADDMONTHS(Date__c,1)), 1) = 1^st March 2022
• DATE(YEAR(ADDMONTHS(Date__c,1)), MONTH(ADDMONTHS(Date__c,1)), 1) – 1 = 28^th Feb, 2022

4. Displaying the Current Time as Text

Let’s create a text formula field that displays the current time as part of a string. To convert the time value to a text value let’s use the function TEXT().

" The time is " & TEXT( TIMENOW() ) & " right now."

5. Display Current Day as Text

Let’s create a text formula field that displays the current day as a string.

" Today is " &
 
 CASE ( WEEKDAY(TODAY()),
        1, "Sunday",
        2, "Monday",
        3, "Tuesday",
        4, "Wednesday",
        5, "Thursday",
        6, "Friday",
        "Saturday")

Common Issues & Errors with Date, Date/Time, and Time Formulas

Facing errors while working with Formula Fields is quite natural, and once you get the hang of it, you’ll be pro at locating errors in your formulas, but until you are, let’s take a look at some of the common errors you may face and how to tackle them.

1. Time Zones

Date/Time and Time values are stored as GMT but are displayed in the time zone of the user viewing them. When working with Date/Time fields and functions, let’s keep a few points in mind:

• DATETIMEVALUE(date) – Can be used to convert a Date value to a Date/Time value. When using this function the time will be set to 12:00 am in GMT.
• TEXT() – When converting a Date/Time value to a string using the TEXT() function. For example, TEXT(NOW()), normally the NOW() function converts to the user’s time zone before being displayed, but since it’s being converted to a text, the time zone conversion will not happen. When we convert a Date/Time to Text, a “Z” is included to the end of it to indicate GMT. Example: 2022-09-06 01:21:10Z

2. Conversion between Date, Date/Time, and Time values

Date, Date/Time, and Time values aren’t compatible. To convert between them, use the following functions:

• DATETIMEVALUE()
• DATEVALUE()
• TIMEVALUE()

If a formula field expects a return type of Time, a formula that returns a Date/Time value will result in a Syntax Error. Ensure that the Formula Return Type and the value that the Formula Field returns is of the same type.

3. Leap Years or Invalid Dates

Ensure that your formula returns a valid date. Example: September 31, 2022 (a non-existent date) or February 29, 2025 ( a non-leap year) would result in an error.

Image Source