Use Date, Date/Time, and Time Formulas
- Describe what the Date, Date/Time, and Time types are.
- Explain the difference between the Date, Date/Time, and Time types.
- Describe use cases for formulas with the Date, Date/Time, and Time return types.
- Create a formula using the Date, Date/Time, and Time types.
When it comes to managing your organization, Date and Date/Time fields are great for showing the birth date of a contact, the age of a case, or the date and time an account was created. Time fields are great for tracking clock times, like business hours. You can use a formula to display the current date or time or find the difference between two dates or times.
A cross-object formula displays data from one object on a different object. In this case, we’re showing the CreatedDate of the account object on the case object. Members of your organization can see a formula that displays or references a cross-object field even if they do not have access to the object that the field is on. For more information about cross-object formulas, see Tips on Building Cross-Object Formulas.
A date is represented as either a Date or Date/Time value. A Date value stores a year, month, and day. A Date/Time value stores a year, month, day, and a time. The time is stored as GMT, but displays in the time zone of the user viewing it. It’s important to keep time zone conflicts in the back of your mind as you work with Date/Time values in formulas. Time zone mix-ups can easily cause logical errors in your formula fields and misinformation in your organization.
If you’re subtracting two Date values, the result is a whole number. If you’re subtracting two Date/Time values, the result is a decimal value representing the number of days, hours, and minutes. For example, if the difference between two Date/Time values is 3.52, there are 3 days, 12 hours (.5 of one day) and 29 minutes (.02 of one day) between the two dates.
A Time value stores the hours, minutes, seconds, and milliseconds (HH:MM:SS.MS). Notice milliseconds follow a period (.) and not a colon (:). A Time value is like a Date/Time value without the date. If you’re subtracting two Time values, the result is in milliseconds and is always positive.
TimeField1__c has the value 10:00 PM and TimeField2__c has the value 9:00 PM:
TimeField1__c - TimeField2__c = 3600000
The result is never a negative number. Subtraction is the elapsed time between two time values, using a 24-hour clock.
Salesforce provides several functions that allow you to work with Date, Date/Time, and Time values more easily. You can convert a Date/Time to a Date by using the DATEVALUE() function, which takes a Date/Time or Text value and returns a Date. Similarly, DATETIMEVALUE() takes a Date or Text value and returns a Date/Time value, with the time set to midnight GMT. And you can convert Date/Time or Text values to Time using the TIMEVALUE() function.
DATE(2015, 03, 17)
To find the current day as a Date value, use TODAY(). To find the current moment as a Date/Time value, use NOW(). These functions are useful for finding dates in the future or past, or how many days away from today another date is.
To take just the day, month, or year from a Date value as a number, use DAY(), MONTH(), or YEAR(), respectively.
One of simplest uses for Date values is finding the number of days between two dates. When you subtract one Date value from another, you get the difference in days as a number.
TODAY() - DATEVALUE(CreatedDate)
Notice that we used DATEVALUE() to convert CreatedDate, a Date/Time value, to a Date value, allowing us to subtract CreatedDate from TODAY(). Date and Date/Time values aren’t compatible. You can’t subtract a Date from a Date/Time, or a Date/Time from a Date, without first converting one of the values.
You can also add days to a Date. To create a formula that returns a date three days after today’s:
- In Setup, use the quick find box to find the Object Manager.
- Click and click New.
- Select Formula and click Next.
- In Field Label, enter Future Date. Field Name populates automatically.
- Select Date and click Next.
- Enter the following
TODAY() + 3
Pretty simple, right? Things get a little more complex when you want to add business days to a date. For this formula, we use the CASE() function to add 3 business days to TODAY(). CASE() is similar to IF() in some ways. The key difference is that IF() checks just one logical statement, while CASE() goes through a series of statements, called cases.
CASE(expression, case1, result1, case2, result2, ... , else_result) compares expression to case1. If they’re equal, it returns result1. If they’re not, it compares expression to case2, and so on. If expression doesn’t match any case, the statement returns else_result.
CASE() is useful for conditional statements that have many possible outcomes. Although you can usually do the same thing with nested IF() statements, CASE() makes complicated formulas easier to read and understand.
We’re going to use CASE() to write a formula that adds 3 business days to TODAY(). The first step when working with CASE() is to think of cases. Our formula could encounter a few different situations:
- If TODAY() is a Sunday, Monday, or Tuesday, adding 3 business days is the same as TODAY() + 3.
- If TODAY() is a Wednesday, Thursday, or Friday, adding 3 business days is the same as TODAY() + 5 (3 business days and 2 weekend days).
- If TODAY() is a Saturday, adding 3 business days is the same as TODAY() + 4 (3 business days and 1 weekend day).
For our formula to work, we have to know what day of the week TODAY() is. Use the WEEKDAY() function to find the day of the week.
CASE( WEEKDAY(TODAY()), 3, TODAY() + 2 + 3, 4, TODAY() + 2 + 3, 5, TODAY() + 2 + 3, 6, TODAY() + 1 + 3, TODAY() + 3 )
You can use variations of this formula to add any number of business days to a date. No matter how many business days you are adding, there is a maximum of seven cases, one for each day of the week.
You can perform math operations on Time values, too.
The unit for adding or subtracting time values is milliseconds.
When Timefield1__c has the value 5:00 PM:
- Timefield1__c + 600000 is 5:10 PM
- Timefield1__c - 600000 is 4:50 PM
Time fields do not include a date. So adding 25 hours to a time value is the same as adding 1 hour. The clock restarts after 24 hours.
Remember, as you can subtract one time field from another in a formula, the result is in milliseconds and the result is never a negative number.
(ClosedTime - OpenTime) / 3600000
And, for the following times, you get the following results.
ClosedTime = 5:00 PM, OpenTime = 8:00 AM, ClosedTime - OpenTime is 9 hours.
ClosedTime = 5:00 AM, OpenTime = 7:00 AM, ClosedTime - OpenTime is 22 hours.
Shift_Ends_Time__c <= (Shift_Start_Time__c + 14400000)
- This formula finds the last day of a month by subtracting a day from the first day of the
following month. It uses the ADDMONTHS() function to
calculate each following month before the subtraction.
DATE(YEAR(ADDMONTHS(Date__c,1)), MONTH(ADDMONTHS(Date__c,1)), 1) - 1
- This formula finds the number of business days between TODAY() and a Date__c before today. First
the formula finds the number of business days between TODAY() and a known Monday in the past, in this case, January 8, 1900. We divide
that number by 7 and use FLOOR() to convert it from
days to weeks. We then multiply it by 5 to convert it from weeks to business days. The
formula finds the difference between that amount and the number of business days since
Date__c and the same reference Monday. The result is
the number of business days between TODAY() and a past
(5 * (FLOOR((TODAY() - DATE(1900, 1, 8)) / 7)) + MIN(5, MOD(TODAY() - DATE(1900, 1, 8), 7))) - (5 * (FLOOR((Date__c - DATE(1900, 1, 8)) / 7)) + MIN(5, MOD(Date__c - DATE(1900, 1, 8), 7 )))
- This formula adds Num_Years__c to Date__c. It uses ADDMONTHS(), which automatically handles leap years.
ADDMONTHS(Date__c, Num_Years__c * 12)
- This formula on Cases checks if reps are calling customers at a preferred time. The
Preferred Time field represents the hour in which a Contact has specified they prefer to be
contacted for outbound Cases.
IF( IF((HOUR(TIMEVALUE(CreatedDate)) - 7) < 0, 24 + (HOUR(TIMEVALUE(CreatedDate)) -7), (HOUR(TIMEVALUE(CreatedDate)) - 7)) = HOUR(Contact.Preferred_Time__c), TRUE, FALSE)
- Converting between Date, Date/Time, and Time. The Date, Date/Time, and Time data types are not interchangeable. If it’s written to return a Date/Time value, a formula with the Date return type doesn’t work. To convert between Date, Date/Time, and Time, use the built-in functions DATEVALUE(), DATETIMEVALUE(), and TIMEVALUE(). Remember that TODAY() returns the current date as a Date value, while NOW() returns the current date and time as a Date/Time value. And TIMENOW() returns the current date and time as a Time value.
- Working with time zones. Date, Date/Time, and Time values always display in the time zone of the user who is viewing them. Date/Time values are converted to and stored as GMT when a record is saved and then converted again when a user outside of GMT views them. When you convert a Date to a Date/Time, the time is always midnight GMT. Be sure to consider time zones when converting a Date/Time value with TEXT(). When you convert a Date/Time to text, it returns the time in GMT, indicated by a Z at the end of the value. TEXT() always generates a text value with the time in GMT—not the time zone of your organization. Time values have no associated locale or time zone, and are not converted between users in different time zones. When you convert a Date/Time to Time using TIMEVALUE(), the GMT value is returned.
- Leap years and invalid dates. When converting Text to Date or Date/Time values or adding years, months, or days to a Date or Date/Time, make sure that your result is still a valid date. For example, if your formula has the result June 31, 2015, the formula field displays #Error! Similarly, if your formula returns February 29, 2013 (a non–leap year), the field displays an error. Ensure that your formulas account for leap years and the varying lengths of months.