Using Date, Date/Time, and Time Values in Formulas
Use Date, Date/Time, and Time values in formulas to manage payment deadlines, calculate contract ages, and support other time-dependent business needs. Learn how to create and compare these values, convert between data types and text, and account for time zones.
Required Editions
| Available in: both Salesforce Classic and Lightning Experience |
| Available in: Essentials, Group, Professional, Enterprise, Performance, Unlimited, and Developer Editions |
How Date, Date/Time, and Time Values Work in Formulas
Salesforce uses three data types for working with dates and times:
- Date: Stores a year, month, and day. Most date values you work with are this type.
- Date/Time: Stores a date together with a time, precise to the second. Salesforce stores the value in Greenwich Mean Time (GMT) and shows it in the user's time zone. CreatedDate is an example of a Date/Time field.
- Time: Stores a time of day on its own, not tied to a date, down to the millisecond. For example, use it to represent business opening and closing hours.
Reports and record detail pages show all three types of values in the user’s locale.
Date calculations answer everyday questions: how many days until a payment is due, how long a contract has been open, or how much time passed between two events. Use addition and subtraction operations on Date, Date/Time, and Time values to calculate future dates or the elapsed time between two dates or times. Subtracting one date from another, for example, returns a value that’s the difference between the two initial values in days (number data type). The same operation between two Date/Time values returns a decimal value indicating the difference in number of days, hours, and minutes. The same operation between two Time values returns milliseconds.
For a concrete example, say the difference
between two Date/Time values is 5.52. That means five days, 12 hours (0.5 of a day),
and 28 minutes (0.02 of a day) separate the two values. You can also add numeric
values to Dates and Date/Times. For example, the operation TODAY() +
3 returns three days after today’s date.
Complex date functions tend to compile larger than text or number formula functions, so formula compile size issues are a possibility.
For guidance specific to the Time field type, including how Time fields behave in formulas, see Considerations for the Time Custom Field Type.
The examples in this topic use date and date/time variables in place of actual Date and Date/Time fields or values.
Get Current Date and Time Values with TODAY(), NOW(), and TIMENOW()
The TODAY() function returns the current day, month, and year as a
Date data type. Use TODAY() to show how many days have passed since
a previous date, or the date of a set number of days in the future. It's also useful
for simply showing the current date.
The NOW() function returns the Date/Time value of the current
moment. It’s useful for dealing with specific times of day, or the date.
The TIMENOW() function returns a value in GMT representing the
current time without the date. Use this function instead of the
NOW() function if you want the current hour, minute, seconds,
or milliseconds. This value is useful for tracking time like work shifts or elapsed
time.
For details on how to convert between Date values and Date/Time values, see Converting Between Date/Time and Date.
Create a Date from Year, Month, and Day Values with DATE()
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. Similarly, DATE( YEAR( TODAY() ),
MONTH( TODAY() ) + 3, 1) returns the Date value of the first day three
months from today in the current year, assuming the date is valid (for example, the
month falls between 1 and 12).
If the inputted Y/M/D values result in an invalid date, the DATE()
function returns an error, so error checking is an important part of working with
Date values. Learn about methods for handling invalid dates in Sample Date Formulas.
Compare Date and Date/Time Values
Date and Date/Time values support the standard comparison operators: equal (= or ==), not
equal (<> or !=), less than (<), greater than (>), less than or equal
(<=), and greater than or equal (>=). Compare Date values with other Date
values and Date/Time values with other Date/Time values. If the types differ,
convert one value before comparing them. A comparison returns TRUE
or FALSE, so it's typically used inside an IF()
function or a validation rule.
To compare a field against a specific date or moment, first construct that value with a
function. Use DATE() to build a date and
DATETIMEVALUE() to build a date/time.
Formulas don't support Date or Date/Time literals. Salesforce treats a value written as
2025-01-01 as a mathematical expression that evaluates to
“2023” instead of a Date. To represent a fixed Date, use DATE() or
DATETIMEVALUE(). To represent a fixed Date/Time, use
DATETIMEVALUE().
For example, to check whether a date is before January 1, 2025:
My_Date__c < DATE( 2025, 1, 1 )To check whether a date/time is before noon GMT on January 1, 2025:
My_Date_Time__c < DATETIMEVALUE( "2025-01-01 12:00:00" )Use the comparison directly in a checkbox formula field. You can also use it in a validation
rule, but remember that the validation error occurs when the comparison returns
TRUE. Wrap the comparison in IF() when you’re
returning different values based on the result.
IF( My_Date__c < DATE( 2025, 1, 1 ), "Before cutoff", "On or after cutoff" )Because DATETIMEVALUE() interprets the string in GMT, the constructed value
is in noon GMT—not the user's time zone. When you compare it against a stored
Date/Time field (also stored in GMT), both sides are in GMT and the comparison is
consistent. For more about time zone behavior, see the Account for Time Zones in Date/Time Formulas section.
To test whether a Date or Date/Time field is empty, use
ISBLANK() rather than comparing to null.
Convert Between Date/Time and Date
Date and Date/Time aren’t interchangeable data types. To perform operations between Date and
Date/Time values, convert the values so they’re both the same type. Some functions
(such as YEAR(), MONTH(), and
DAY()) also only work on Date values, so convert Date/Time
values first.
Use the DATEVALUE( date/time
) function to return the Date value of a Date/Time. For example, to get
the year from a Date/Time, use YEAR( DATEVALUE( date/time
) ).
To calculate the number of calendar days between today and a record’s creation date, convert CreatedDate from Date/Time to Date:
TODAY() - DATEVALUE( CreatedDate )TODAY()
returns a Date, while CreatedDate is a Date/Time.
DATEVALUE() makes both operands Date values before
subtraction.
If a formula references a Date/Time field but returns a Date, it ignores the time component
without converting it to the user’s time zone. This behavior can result in an
incorrect date. To correct for the user’s time zone, use the
DATEVALUE() function to convert the Date/Time field.
You can convert a Date value to a Date/Time by using the DATETIMEVALUE(
date ) function. The system sets the time to
12:00 a.m. in Greenwich Mean Time (GMT). And then it’s converted to the time zone of
the user viewing the record. For a user in San Francisco, DATETIMEVALUE(
TODAY() ) returns 5:00 p.m. on the previous day (during Daylight Saving
Time) rather than 12:00 a.m. of the current day. For examples of how GMT-based
Date/Time conversions affect formula results and guidance on accounting for time
zones, see the Account for Time Zones in Date/Time Formulas section.
Convert Between Date and Text
To include a date as part of a
string, wrap the Date value in the TEXT() function to convert it to
text. For example, to return today’s date as text,
use:
"Today's date is " & TEXT( TODAY() )This formula returns the date in the format “YYYY-MM-DD” rather than in the locale-dependent format.
To show a date as text with its components in a different order, extract the month, day, and year separately. Then combine them in your preferred order with any separators that you want. This example formats the date portion as “M/D/YYYY” with slashes:
"Today's date is " & TEXT( MONTH( date ) ) & "/" & TEXT( DAY( date ) ) & "/" & TEXT( YEAR( date ) )To
convert text to a Date value, pass a string in “YYYY-MM-DD” format to
DATEVALUE(). This example returns June 1,
2013:
DATEVALUE( "2013-06-01" )Convert Between Date/Time and Text
You can include a
Date/Time value in a string with the TEXT() function.
TEXT() returns the value in GMT rather than converting it to
the user’s time zone. If you expect a local time, this example shows the
issue:
"The current date and time is " & TEXT( NOW() )In this formula, NOW() stays in GMT. Normally NOW() switches to
the user’s time zone when viewed, but converting it to text prevents that change. If
you run this formula at 5:00 PM on August 1 in San Francisco time (GMT-7), the
result is “The current date and time is 2013-08-02 00:00:00Z”.
Converting a Date/Time to text adds a “Z” at the end to indicate GMT. TEXT(
date/time ) returns “Z” when the field is blank.
If it’s possible that the Date/Time value is blank, check for a blank value before
converting to text:
IF(
ISBLANK( date/time ),
"",
TEXT( date/time )
)To convert a string to a Date/Time value, pass it to DATETIMEVALUE() in
“YYYY-MM-DD HH:MM:SS” format. This example returns “2013-08-01” in GMT:
DATETIMEVALUE( "2013-08-01 12:00:00" )Build a Date/Time from a Date and a Time
It’s common to combine separate Date and Time fields into a single Date/Time value—for example,
an incident or appointment date and time. By combining the two fields, you can
perform Date/Time arithmetic on the result. Build the combined value with
DATETIMEVALUE(), which accepts a text string in the exact form
“YYYY-MM-DD HH:MM:SS” and returns a Date/Time in GMT.
Don't concatenate the complete text of the Time field with the Date text. TEXT()
returns a Date as “YYYY-MM-DD”, but returns a Time with fractional seconds—for
example, 17:30:00.000. Because DATETIMEVALUE() expects “HH:MM:SS”,
trim the Time text before combining the values.
Use LEFT() to keep
only the first eight characters of the time text (“HH:MM:SS”), then concatenate the
date, a space, and the trimmed
time:
DATETIMEVALUE( TEXT( Incident_Date__c ) & " " & LEFT( TEXT( Incident_Time__c ), 8 ) )DATETIMEVALUE() interprets the combined text as GMT. It doesn’t apply the time
zone of the user who entered the Date or Time value. For example, combining a date
with 09:00:00 creates a Date/Time representing 9:00 AM GMT, which Salesforce
converts to the viewer’s time zone when viewed. If the Time value represents local
time, account for time zones and daylight saving time before using the result. For
examples of how GMT-based conversions and daylight saving time can affect formula
results, see the Account for Time Zones in Date/Time Formulas
section.
Work with Time Values
The Time data type represents a time of day not tied to a date, such as a business opening or
closing time. Use TIMEVALUE() to extract a Time value from a
Date/Time value or to convert text to Time. Use TEXT() to convert a
Time value to text.
- Convert Date/Time to Time
The
TIMEVALUE()function returns a Time data type value in “HH:MM:SS.MS” (hours:minutes:seconds.milliseconds) format using a 24-hour clock. Numerical H/M/S/MS values and theHOUR(),MINUTE(),SECOND(), andMILLISECOND()functions are valid parameters forTIMEVALUE().Use the
TIMEVALUE(value)function to return the Time value of a Date/Time type, text, merge field, or expression. For example, extract the time from a ClosedDate Date/Time value withTIMEVALUE(ClosedDate). - Convert Between Time and Text
To include time as part of a string, wrap the Time value in the
TEXT()function to convert it to text. For example, to return the current time as text, use:"The time is " & TEXT( TIMENOW() )This function returns the time in the format “HH:MM:SS.MS”.
You can also convert text to a Time data type so you can use the string value with your other Time fields and formulas. Format your text as “HH:MM:SS.MS” on a 24-hour clock. Use the
TIMEVALUE()function:TIMEVALUE("17:30:45.125")
Account for Time Zones in Date/Time Formulas
Salesforce stores Date and Date/Time values in GMT. Saving a record adjusts field values from the user's time zone to GMT, then back to the viewer's time zone in reports and record pages. These adjustments don’t pose a problem with Date conversions because converting a Date/Time to a Date results in the same Date value.
When working with Date/Time fields and values, however, the system always performs the conversion in GMT, not the user’s time zone. Subtracting a standard Date/Time field from another isn’t a problem because both fields are in the same time zone. If a value in the calculation is a conversion from a Text or Date value to a Date/Time value, however, the results are different.
This example contrasts two cases. The first subtracts Date/Time values represented
consistently in GMT. The second uses a Date/Time value created from text, which
DATETIMEVALUE() interprets as GMT without applying the user’s
time zone. Say that a San Francisco user enters a value of 12:00 AM on August 2,
2013 in a custom Date/Time field called Date_Time__c. The
system stores the value as 2013-08-02 07:00:00Z because the time difference in
Pacific Daylight Time is GMT-7. At 12:00 p.m. PDT on August 1, the user views the
record, triggering this
formula:
Date_Time__c - NOW()In the
calculation, NOW() is 2013-08-01 19:00:00Z, and then subtracted
from 2013-08-02 07:00:00Z, to return the expected result of 0.5 (12 hours).
Suppose that instead of NOW(), the formula converts the string “2013-08-01 12:00:00” to a Date/Time value:
Date_Time__c - DATETIMEVALUE( "2013-08-01 12:00:00" )In
this case, DATETIMEVALUE( “2013-08-01 12:00:00” ) is 2013-08-01
12:00:00Z, and returns a result of 0.79167, or 19 hours.
There’s no way to determine a user’s time zone in a formula. Say all of your users are in the same time zone. You can adjust the time zone difference by adding or subtracting the time difference between the users’ time zone and GMT to your converted values. But managing this type of calculation in a formula is difficult. Time zones are affected by Daylight Saving Time (DST), and the start and end dates for DST are different each year. We recommend using Apex for transactions that require converting between Date/Time values and Text or Date values.
