Skip to main content
A Trailblazer Community no Slack está agora em beta aberto. Participe e interaja em tempo real. Junte-se a nós no Slack

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.

Note

DATETIMEVALUE takes either a date or a text value with the format YYYY-MM-DD and a time in UTC. If you input a value outside of the valid range, such as February 29 in a non–leap year or a month greater than 12, your formula field shows #Error!

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.

Note

Be careful to subtract only past dates from future dates. If you reversed the formula—DATEVALUE(CreatedDate) - TODAY()—it would return a negative value.

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.

  1. From Setup, open the Object Manager and select Opportunity.
  2. Select Fields & Relationships and select New.
  3. Select Formula and then Next.
  4. For Field Label, enter Expected Close Date.
  5. For Formula Return Type select Date.
  6. Click Next.
  7. Select Advanced Formula.

Let’s get the DATEVALUE for Created Date since it is a date/time value and add 21 days to it.

  1. Start by inserting the DATEVALUE function.
  2. For the DATEVALUE expression parameter, insert the Created Date field.
  3. Right after the DATEVALUE closing parenthesis, insert the + Add operator.
  4. Enter 21. This is the number of days in three weeks.
    Your formula should be:
    DATEVALUE( CreatedDate ) + 21
  5. Select Check Syntax.
  6. For Description, enter Calculates the expected close date based on three weeks from the created date.
  7. Keep Treat blank fields as zeroes selected.
  8. 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)
Note

ADDMONTHS considers the last day of a month when performing math operations. So when you add a month to a date that is the 30th, and the last day of the month, the result is the last day of the next month. This resulting value could be the 31st. Similarly, February 28th (in a non–leap year) plus one month is March 31st.

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.

Note

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. However, a time value’s precision is in milliseconds. A date/time value’s precision is in seconds.

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

Note

There are 1,000 milliseconds in a second. To get the minutes you would divide by 6,000.

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.

Resources

Compartilhe seu feedback do Trailhead usando a Ajuda do Salesforce.

Queremos saber sobre sua experiência com o Trailhead. Agora você pode acessar o novo formulário de feedback, a qualquer momento, no site Ajuda do Salesforce.

Saiba mais Continue compartilhando feedback