Work with Dates and Times
Learning Objectives
After completing this unit, you’ll be able to:
- Explain the difference between the date, date/time, and time return types.
- Create a formula using the date return type.
- Identify common errors that can occur using the date, date/time return types.
Date, Date/Time, and Time in Formulas
When it comes to managing your organization, date and date/time fields are great for showing the age of a case or the date and time an account was created. Both data types, date and date/time, can be used together in formulas, allowing you to do cool things like add or subtract them to calculate future dates or time spans. But, they’re not always interchangeable. So, you might need to do a little converting or formatting to make sure they work well together.
Salesforce stores date values and date/time values. A date value stores a year, month, and day. A date/time value stores a year, month, day, and a time. 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.
Common Date, Date/Time, and Time Functions and Operators
Salesforce provides several functions that allow you to work with date, date/time, and time values more easily. Let’s say you need to display a date for the first day of the month nine months from the Created Date of an Opportunity. The DATE function returns a Date value, given a year, month, and day. Numerical Y/M/D values and the YEAR, MONTH, and DAY functions are valid parameters for DATE. For example DATE( 2013, 6, 1 ) returns June 1, 2013. You can solve the nine months from Created Date by combining some of the other date functions with the DATE function like this:
DATE( YEAR( ADDMONTHS( DATEVALUE( CreatedDate ),9 ) ), MONTH( ADDMONTHS( DATEVALUE( CreatedDate ),9 ) ), 1)
This returns the date value of the first day nine months from the Created Date field. Using the ADDMONTHS function takes care of rounding to a new year if the number of months added takes the date into the new year.
The formula uses the DATEVALUE function to convert the date/time value CreatedDate to a date value that ADDMONTHS needs to work with. The DATEVALUE function takes a date/time or text value input and returns a date value.
Similarly, the DATETIMEVALUE function takes a date or text value input and returns a date/time value, with the time set to midnight Coordinated Universal Time or UTC. And you can convert date/time or text values to a time value using the TIMEVALUE function.
To find the current day as a date value, use the TODAY function. To find the current moment as a date/time value, use the NOW function. 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 the DAY, MONTH, or YEAR functions, respectively.
Time fields use the TIMEVALUE function similarly to DATEVALUE. Say you want to set a time field default value to 5:30 PM. Use the international date format in a formula for the field’s default value.
TIMEVALUE("17:30:00.000")
And to get the current time, use the TIMENOW function. You also have the HOUR, MINUTE, SECONDS, and MILLISECONDS functions. For example, if you’re tracking minutes for a timer, use both TIMENOW and MINUTE to get only the minutes of current time.
MINUTE(TIMENOW())
Use the Date, Date/Time, and Time Types in Formulas
One of the simplest ways to use date values is to find the number of days between two dates. When you subtract one date value from another, you get the difference in days as a number.
If you want to find out how many days there are between today’s date and the CreatedDate of an account, for example, use:
TODAY() - DATEVALUE(CreatedDate)
Notice that we use DATEVALUE to convert CreatedDate, a date/time value, to a date value, which allows us to subtract CreatedDate from TODAY. Date and date/time values aren’t compatible. You can’t subtract a date value from a date/time value, or a date/time value from a date value, without first converting one of the values.
Here’s an interesting scenario. The sales team has requested that you add a field to Opportunities showing the expected close date. The expected close date is three weeks after the date the opportunity was added to the system. Should be pretty straightforward. Let’s do it. You’ll use the Created Date and add three weeks worth of days to it.
- From Setup, open the Object Manager and select Opportunity.
- Select Fields & Relationships and select New.
- Select Formula and then Next.
- For Field Label, enter
Expected Close Date. - For Formula Return Type select Date.
- Click Next.
- Select Advanced Formula.
Let’s get the DATEVALUE for Created Date since it is a date/time value and add 21 days to it.
- Start by inserting the DATEVALUE function.
- For the DATEVALUE
expressionparameter, insert the Created Date field. - Right after the DATEVALUE closing parenthesis, insert the + Add operator.
- Enter 21. This is the number of days in three weeks.
Your formula should be:DATEVALUE( CreatedDate ) + 21 - Select Check Syntax.
- For Description, enter
Calculates the expected close date based on three weeks from the created date. - Keep Treat blank fields as zeroes selected.
- Select Next, Next, and Save.
Well done! The sales team is excited about the new insights for the opportunities.
Things get a little more complex when you want to add business days to a date. For this formula, use the CASE function to add 3 business days to the record Created Date field.
The first step when working with the CASE function is to think of the cases. The CASE function is used to evaluate an expression against a series of values and return a result that corresponds to the first matching value or if no match then returns the final result. The formula could encounter a few different situations:
- If CreatedDate is a Sunday, Monday, or Tuesday, adding 3 business days is the same as CreatedDate + 3 days.
- If CreatedDate is a Wednesday, Thursday, or Friday, adding 3 business days is the same as CreatedDate + 5 days (3 business days and 2 weekend days).
- If CreatedDate is a Saturday, adding 3 business days is the same as CreatedDate + 4 days (3 business days and 1 weekend day).
For our formula to work, we have to know what day of the week the Created Date is. Use the WEEKDAY function to find the day of the week. WEEKDAY returns the day of the week for the given date, using 1 for Sunday, 2 for Monday, through 7 for Saturday.
The CASE function then decides on an outcome based on the WEEKDAY of CreatedDate. WEEKDAY works with date values so Created Date needs to be transformed to a date value using the DATEVALUE function. The final formula looks like this:
CASE(WEEKDAY(DATEVALUE(CreatedDate)), 4, CreatedDate + 5, 5, CreatedDate + 5, 6, CreatedDate + 5, 7, CreatedDate + 4, CreatedDate + 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. As you can subtract one time field from another in a formula, the result is in milliseconds and the result is always a positive number.
For example, when calculating the number of hours a business is open, you use the following formula.
(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.
Date, Date/Time, and Time Examples
- 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(CreatedDate,1)), MONTH(ADDMONTHS(CreatedDate,1)), 1) - 1
- This formula adds Num_Years__c to CreatedDate. It uses ADDMONTHS, which automatically handles leap years.
ADDMONTHS(CreatedDate, Num_Years__c * 12)
Common Issues with Date, Date/Time, and Time Formulas
Converting Between Date, Date/Time, and Time
The date, date/time, and time data types are not interchangeable. If the formula is 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 the TODAY function returns the current date as a date value, while the NOW function returns the current date and time as a date/time value. And the TIMENOW function returns the current time as a time value.
Time Zones
Keep in mind that time data is stored in the org as UTC (Universal Time Coordinated), but users see it displayed for their own time zone. 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.
Subtraction
When you subtract one date value from another, the result is a whole number. When you subtract one date/time value from another, 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) then the difference is displayed as 3.52 between the two dates.
When you subtract one time value from another, the difference (the result) is the elapsed time between the two values using a 24-hour clock. The resulting value is given in milliseconds and it’s always a positive number. For example, if TimeField1__c has the value 10:00 PM, and TimeField2__c has the value 9:00 PM, then:
TimeField1__c - TimeField2__c = 3600000
Leap Years and Invalid Dates
When converting text values 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.
Wrap It Up
You now know how to work with date, date/time, and time values. Review the resources to go further. You can even start adding some specific data insights to help your organization.
