---
schema_version: "1"
id: 154fb3480b340c8209cf0bb78ec474b446ac37cb0b6690feb3ee41ce21a836c5
title: Using Date, Date/Time, and Time Values in Formulas
collection: admin
version: current
locale: en-us
portal_url: https://docs.salesforce.com/admin/current/en-us/platform/264-0-0/formula_using_date_datetime.md
canonical_url: https://help.salesforce.com/s/articleView?id=platform.formula_using_date_datetime.htm&release=264.0.0&type=5
source_site: help.salesforce.com
content_type: documentation
content_hash: f5cd661d9b2eed468e15b98f834e6249c8d4659aab87aedd0cd2ffb1e0555555
description: 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.
products:
  - Salesforce Platform
  - Security
release: "264"
---
# 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

<table class="slds-table slds-table_bordered slds-max-medium-table_stacked slds-m-bottom_small edition"><colgroup><col></colgroup><tbody><tr class=""><td class="slds-cell-wrap" style="vertical-align:top;">Available in: both Salesforce Classic and Lightning Experience</td></tr><tr class=""><td class="slds-cell-wrap" style="vertical-align:top;">Available in: <strong>Essentials</strong>, <strong>Group</strong>, <strong>Professional</strong>, <strong>Enterprise</strong>, <strong>Performance</strong>, <strong>Unlimited</strong>, and <strong>Developer</strong> Editions</td></tr></tbody></table>

## 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.

![Tip](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-platform-extend_click-264-0-0-production-enus/5f7d9219-b7b1-4552-8891-f1aba9b289fc/images/icon_tip.png)

Tip For sample formulas for common date calculations, such as counting business days between two dates or adding a specified number of months to a date, see [Sample Date Formulas](https://help.salesforce.com/s/articleView?id=platform.formula_examples_dates.htm&release=264.0.0&type=5 "Use Date, Date/Time, and Date formulas for many common business needs. For example, extract and format parts of a date or time, such as quarter, week, weekday, month name, or the hour, minute, and second. Calculate durations in days, business days, or business hours. Project future and relative dates, and handle issues such as leap years, the last day of the month, and time zone conversions.").

For guidance specific to the Time field type, including how Time fields behave in formulas, see [Considerations for the Time Custom Field Type](https://help.salesforce.com/s/articleView?id=platform.custom_field_time_overview.htm&release=264.0.0&type=5 "Track time, unbound to a date, with the Time custom field type, which is useful for time management, event planning, and project management.").

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](https://help.salesforce.com/s/articleView?id=platform.formula_using_date_datetime.htm&release=264.0.0&type=5).

## 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](https://help.salesforce.com/s/articleView?id=platform.formula_examples_dates.htm&release=264.0.0&type=5 "Use Date, Date/Time, and Date formulas for many common business needs. For example, extract and format parts of a date or time, such as quarter, week, weekday, month name, or the hour, minute, and second. Calculate durations in days, business days, or business hours. Project future and relative dates, and handle issues such as leap years, the last day of the month, and time zone conversions.").

## 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](https://help.salesforce.com/s/articleView?id=platform.formula_using_date_datetime.htm&release=264.0.0&type=5) section.

![Note](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-platform-extend_click-264-0-0-production-enus/5f7d9219-b7b1-4552-8891-f1aba9b289fc/images/icon_note.png)

Note

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.

![Note](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-platform-extend_click-264-0-0-production-enus/5f7d9219-b7b1-4552-8891-f1aba9b289fc/images/icon_note.png)

Note

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](https://help.salesforce.com/s/articleView?id=platform.formula_using_date_datetime.htm&release=264.0.0&type=5) 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](https://help.salesforce.com/s/articleView?id=platform.formula_using_date_datetime.htm&release=264.0.0&type=5) 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 the `HOUR()`, `MINUTE()`, `SECOND()`, and `MILLISECOND()` functions are valid parameters for `TIMEVALUE()`.
    
    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 with `TIMEVALUE(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.

#### See Also

- [TIMEVALUE](https://help.salesforce.com/s/articleView?id=platform.customize_functions_timevalue.htm&release=264.0.0&type=5 "Returns the time value without the date, such as business hours.")
- [DATEVALUE](https://help.salesforce.com/s/articleView?id=platform.customize_functions_datevalue.htm&release=264.0.0&type=5 "Returns a date value for a date/time or text expression.")
- [DATETIMEVALUE](https://help.salesforce.com/s/articleView?id=platform.customize_functions_datetimevalue.htm&release=264.0.0&type=5 "Returns a year, month, day, and GMT time value.")
- [Tips for Working with Date and Date/Time Formulas](https://help.salesforce.com/s/articleView?id=platform.tips_for_using_date_datetime_formula_fields.htm&release=264.0.0&type=5 "Use these tips to avoid unexpected results in Date and Date/Time calculations. Learn about data type compatibility, calculating durations and intervals, handling fractional day values, and staying within valid year ranges.")
- [Tips for Building Formulas](https://help.salesforce.com/s/articleView?id=platform.tips_on_building_formulas.htm&release=264.0.0&type=5 "Extend your Salesforce data with powerful formula logic. Access a collection of tips for using specific data types in your formulas, including dates, numbers, text, picklists, and record types.")
- [Considerations for the Time Custom Field Type](https://help.salesforce.com/s/articleView?id=platform.custom_field_time_overview.htm&release=264.0.0&type=5 "Track time, unbound to a date, with the Time custom field type, which is useful for time management, event planning, and project management.")