Formulas | Formula field data types

Date

Subtract time from and add time to a date

You can use operations like addition and subtraction on date values to calculate a future date or the elapsed time between two dates.

To calculate a date by adding time to an existing date, you can insert a date, the add operator + and the number of days you wish to add. For example, to add 7 days to a Start Date on the Employment Record for a one week probation period:

fHCM2__Start_Date__c + 7

To subtract, use the subtract operator – instead of the add operator +.

The ADDMONTHS function

To add months to a date, use the ADDMONTHS function:

ADDMONTHS(date,num)

Replace the text date with the date you want to you insert and the text num with the number of months you want to add to the date. Both the date and the number of months can be fields or values.

For example, to calculate a 3 month probation period from the start date, you can insert a number, or use a field that contains it, like the customer Probation_Months__c field in the second example: 

ADDMONTHS(fHCM2__Start_Date__c,3)

ADDMONTHS(fHCM2__Start_Date__c,Probation_Months__c)

When you use ADDMONTHS, the date returned has the same calendar day with the number of months changed.

In the probation months example, if the Start Date is 1 January 2019, adding 3 months with the ADDMONTHS function returns 1 April 2019. As Probation End is usually the day before, the probation end date calculation needs to have 1 day subtracted:

ADDMONTHS(fHCM2__Start_Date__c, Probation_Months__c) - 1

The output type of this formula is date.

The TODAY function

The TODAY function returns the current date as a Date data type. This function is useful for formulas where you want to: 

• Know how many days have passed since a previous date

• Know the date of a certain number of days in the future

• Display the current date

For example, the following example returns a date three days from today's date::

TODAY() + 3

Using yyyy and YYYY for the year

When you reference date values in formulas, keep in mind values often mean slightly different things in lower case and upper case. In terms of years, yyyy returns to the calendar year of the data in question. For example, NOW returns the year of the current date and time. YYYY, on the other hand, determines the year by evaluating the week of the date, and returning the year from it. In the majority of cases, the returned year is the same in both cases, but can be different for dates at the end of a year.

Time and date/time

Time is independent of the date for tracking time such as business hours. Date/Time fields hold a date value and a time value. The time in a date/time field is stored with reference to GMT but displayed in the users’ time zone.

You can use operations like addition and subtraction on date/time and time values to calculate elapsed time between the two values. If you subtract one date/time from another date/time, the resulting value returns a decimal value indicating the difference in a number of days, hours, and minutes. The same operation between two Time values returns milliseconds.

For example, if the difference between two date/time values is 5.52, that means the two values are separated by five days, 12 hours (0.5 of a day), and 28 minutes (0.02 of a day).

Converting between date/time and date

Date and date/time are not interchangeable data types. If you want to perform operations between date and date/time values, such as comparing the date, you need to extract the values first so they are both the same type.

Functions such as YEAR(), MONTH(), and DAY() only work on date values. To extract the year, month or day from a date in a date/time value, you first need to convert it. To extract a date from a date/time field use DATEVALUE(expression) and replace the text expression with the date/time field.

For example, to build a formula to get the year from the Interview Date date/time field in an Application record:

1. First insert the YEAR(date) function.

2. Then highlight the text date and insert the DATEVALUE function YEAR(DATEVALUE(expression))

3. Then highlight the text expression and insert your date/time field. The formula looks like this example:

   Copy

   YEAR(DATEVALUE(fRecruit__Interview_Date__c))

The output type of this formula is text, because it returns the year only, rather than a date.

Time zones

Salesforce stores date/time values as GMT time but displays them to users in the time zone specified on their User record. When a record is saved, field values are adjusted from the user’s time zone to GMT, and then adjusted back to the viewer’s time zone when displayed in record detail pages and reports.

For example, if a User is in the Eastern Standard Time (EST) time zone, which is 5 hours behind GMT, and they enter a time in the system as 09:00, on saving Salesforce converts it to 14:00 GMT. When a user with the EST time zone views the saved time, it displays as 09:00, as Salesforce converts it for the viewer. A user with the GMT time zone see the time as 14:00. For this reason, whenever you use date/time fields, consider that users can have different time zones specified in their User records, and what impact it can have on their use of the field.

When working with date/time fields and values, all calculations in formulas use the GMT time zone. This is not an issue in basic addition and subtraction operations because both date/time values are in GMT. But if you convert a text or a date value into a date/time value, or you use the NOW() operator with a date/time to convert the value into text, you need to consider the time zones, and adjust if necessary. For example, to compare the value of Make Offer Date field, which is a date field, and the Interview Date field, which is a date/time field, you need to consider that Interview Date is stored as GMT and Make Offer Date is not.

Number

The MAX and MIN functions

The MAX function returns the highest number from a list of numbers, and the MIN function returns the lowest.

Text

You can display entered text in a formula field with the output type of text by surrounding the text with quotation marks.

You can also use formula fields with the output type of text to display values of other fields, such as picklists or dates, by using the TEXT() function.

By selecting a formula output type as text and including text that you wish to display in a formula surrounded by quotation marks, text can be displayed in a formula field. If the text you wish to display another field, such as a picklist or date as text, you can use Text() and include the field inside the brackets.

Concatenate operator and BR function

The concatenate operator & enables you to combine the values of multiple fields into one.

The concatenate operator is often used to combine the name fields and address fields into one field, which can be particularly helpful for PDFs and email templates.

To concatenate fields, start by inserting the first field, then insert the operator. If you then insert the second field there will be no spaces between the first and second fields in the result so you may need to insert a space, to do so encase the space with speech mark.

For example, to concatenate a Team Member’s Full Name from their First Name and Surname looks like the following example: 

fHCM2__First_Name__c & " " & fHCM2__Surname__c

The BR() function inserts a line break. Line breaks can often be helpful within concatenated fields for address, or where you have items to display in a list. For example, if you want to display each benefit a team member has on a separate line. To use the BR() function, insert the first field, insert the concatenate operator &, insert the BR() function, insert the concatenate operator & and then insert the field you want to start on a new line.

The following example displays an example of concatenated home address fields with line breaks inserted to have each element on its own line:

fHCM2__Home_Address_1__c & 
BR() & fHCM2__Home_Address_2__c & 
BR() & fHCM2__Home_Address_3__c & 
BR() & fHCM2__Home_Address_City__c & 
BR() & fHCM2__Home_Address_Region__c & 
BR() & fHCM2__Home_Address_Country__c & 
BR() & fHCM2__Home_Address_Postal_Code__c

The result of the formula could look like the example in the following screenshot:

Like the example illustrates, if some of the concatenated address fields are not populated, blank lines are displayed in the formula's value. To avoid this, the ISBLANK() and IF() functions can be combined within the formula above to only display certain address lines when they contain a value, see Common operators and functions for more details.

The combination of the concatenate operator and the BR() function can also be used to display text with line breaks, in addition to fields. For example, to provide clear instructions for a performance review, you can use the following formula to split the instruction text onto different lines:

"Contributions " & 
BR() & "What were the three contributions you made this year that delivered the highest impact?" & 
BR() & "If associated with a deal, please include:" & 
BR() & "1. Deal origination/key relationship management" & 
BR() & "2. Decision made on the deal" & 
BR() & "3. The overall result or outcome"

The result of this formula looks something like the example in the following screenshot:

Other text functions