Use Date, Date/Time and Time Values in Formula

Date formulas serve a crucial purpose in effectively handling payment deadlines, contract durations, or any other time-sensitive aspects within your organization’s operations. Use Date, Date/Time and Time Values in Formula to manage these elements efficiently.

Two distinct data types are employed for handling temporal information: Date and Date/Time. The Date type pertains solely to dates, encompassing the year, month, and day. Meanwhile, Time, another data type, is utilized independently of dates and is employed for tracking temporal elements like business hours. Primarily, values associated with dates are of the Date data type.

Nevertheless, fields like CreatedDate are Date/Time fields, containing both date and time details (stored in GMT but displayed in the user’s local time zone). In reports and record detail pages, Date, Date/Time, and Time fields adhere to the user’s locale formatting. Time values are precise to milliseconds, while Date/Time values are precise to seconds.

You can perform calculations using addition and subtraction with Date, Date/Time, and Time values to predict future dates or determine elapsed time between two points. Subtracting one date from another yields the difference in days (Number data type). Similarly, subtracting two Date/Time values provides the difference in days, hours, and minutes as a decimal. For Time values, subtraction returns milliseconds.

When you subtract two Date/Time values and get a result like 5.52, it signifies that there’s a gap of five days, 12 hours (0.5 of a day), and 28 minutes (0.02 of a day) between them. Moreover, you can perform arithmetic operations with dates and date/times. For instance, if you add 3 to TODAY(), it gives you the date three days from now. To explore further on handling Date, Date/Time, and Time Values in Formula.

In the examples provided, the terms “date” and “date/time” are utilized instead of specific Date and Date/Time fields or values.

Bear in mind that intricate date functions typically result in larger compiled sizes compared to text or number formula functions, which may lead to potential issues with formula compilation size.

TODAY(), NOW() and TIMENOW()

The TODAY() function within the platform developer 1 course provides the present day, month, and year in the form of a Date data type. It proves beneficial in crafting formulas that require tracking the elapsed days from a prior date, forecasting a date a certain number of days ahead, or simply showcasing the current date.

The NOW() function retrieves the current Date/Time value, which proves beneficial when pinpointing specific times of the day along with the date.

The TIMENOW() function outputs a GMT value denoting the present time, excluding the date. Employ this function over NOW() when you solely require the current hour, minute, seconds, or milliseconds. It proves beneficial for monitoring time, such as work shifts or elapsed durations.

The DATE() Function

The DATE() function in Platform Developer 1 returns a Date value based on provided year, month, and day parameters, including numerical values and the functions YEAR(), MONTH(), and DAY(). For example, DATE(2024, 6, 1) yields June 1, 2024. Another example, DATE(YEAR(TODAY()), MONTH(TODAY()) + 3, 1), returns the first day three months from today, provided the resulting date is valid (month is between 1 and 12).

In case the Y/M/D values entered lead to an invalid date, the DATE() function will generate an error. Hence, it’s crucial to include error checking when dealing with Date values. Explore strategies for managing invalid dates in the Sample Date Formulas documentation.

Converting Between Date/Time and Date

In the Platform Developer 1 course, it’s crucial to understand that Date and Date/Time data types are distinct and not interchangeable. To perform operations involving both types, you must convert them to the same type. Functions like YEAR(), MONTH(), and DAY() operate only on Date values, so Date/Time values must be converted first.

Utilize the DATEVALUE( date/time ) function for extracting the Date value from a Date/Time data. To obtain the year from a Date/Time, employ YEAR( DATEVALUE( date/time ) ) as an illustration.

In the Platform Developer 1 course, you can convert a Date value to Date/Time using the DATETIMEVALUE(date) function. This sets the time to 12:00 a.m. GMT and adjusts it to the user’s time zone. For example, DATETIMEVALUE(TODAY()) for a user in San Francisco will yield 5:00 p.m. of the previous day during Daylight Saving Time. For more details, see the “A Note About Date/Time and Time Zones” section.

Converting Between Date/Time and Time

The TIMEVALUE() function is utilized to acquire a time data type value in the “HH:MM:SS.MS” format, representing hours, minutes, seconds, and milliseconds, adhering to a 24-hour time format. Valid parameters for TIMEVALUE() include numerical values for hours, minutes, seconds, and milliseconds, along with the HOUR(), MINUTE(), SECONDS(), and MILLISECONDS() functions.

Utilize the TIMEVALUE(value) function to extract the time value from a Date/Time type, text, merge field, or expression. For instance, you can extract the time from a ClosedDate Date/Time value using TIMEVALUE(ClosedDate).

Converting Between Date and Text

To incorporate a date within a string, utilize the TEXT() function to convert the Date value into text format. For instance, if you aim to display today’s date as text, employ the following syntax:

"Today's date is " & TEXT( TODAY() )

This function retrieves the date in the “YYYY-MM-DD” format, instead of the locale-specific format. To alter the format, extract the day, month, and year from the date initially, then combine them in your desired format. For instance:

"Today's date is " & TEXT( MONTH( date ) ) & "/" & TEXT( DAY( date ) ) & "/" & TEXT( YEAR( date ) ) )

In order to utilize the string value alongside your other Date fields and formulas, it’s advisable to convert text to a Date format. Ensure your text follows the format “YYYY-MM-DD”. Employ this formula to obtain the Date value:

DATEVALUE( "YYYY-MM-DD" )

Converting Between Date/Time and Text

Incorporating Date/Time values into a string is achievable through the TEXT() function, yet it’s crucial to handle time zones cautiously. Take this formula into account for instance:

"The current date and time is " & TEXT( NOW() )

In this expression, the NOW() function is adjusted to GMT. Typically, when NOW() is displayed, it would be adjusted to the user’s time zone, but since it has been converted to text, this adjustment won’t occur. Therefore, if you run this expression on August 1st at 5:00 PM in San Francisco time (GMT-7), the output will be “The current date and time is 2024–08–02 00:00:00Z”.

When converting a Date/Time to text, append a “Z” to denote GMT. If the field is empty, TEXT(date/time) will output “Z”. Therefore, if there’s a possibility of the Date/Time value being empty, verify this prior to conversion.

IF(
	ISBLANK( date/time ),
	"",
	TEXT( date/time )
)

To transform a string into a Date/Time value, employ the DATETIMEVALUE() function with a string structured as “YYYY-MM-DD HH:MM:SS”. This function yields the Date/Time value in Greenwich Mean Time (GMT).

Converting Between Time and Text

To integrate time into a string, utilize the TEXT() function to convert the Time value into text format. For instance, to display the current time as text, employ the following syntax:

"The time is " & TEXT( TIMENOW() )

This function returns the time in the format “HH:MM:SS.MS”.

You’re taught to manipulate text data in Time fields by converting them into the Time data type. This conversion allows seamless integration with other Time fields and formulas. Utilize the TIMEVALUE() function and format your text as “HH:MM:SS.MS” on a 24-hour clock for effective processing.

TIMEVALUE("17:30:45.125")

A Note About Date/Time and Time Zones

Both Date and Date/Time values are stored in Greenwich Mean Time (GMT). Whenever a record is saved, the field values are first converted from the user’s time zone to GMT. Then, when these values are displayed on record detail pages and reports, they are adjusted back to the viewer’s time zone. Fortunately, with Date conversions, there’s no issue because converting a Date/Time to a Date yields the same Date value.

When handling Date/Time fields and values in platform development, it’s essential to note that conversions consistently occur in GMT rather than the user’s local time zone. Subtracting one standard Date/Time field from another poses no issues since both fields operate within the same time zone. However, discrepancies arise when one of the values involved in the calculation stems from converting a Text or Date value to a Date/Time value.

Suppose a user from San Francisco inputs 12:00 AM on August 2, 2024, into a custom Date/Time field named Date_Time_c. This input gets stored as 2024–08–02 07:00:00Z due to the time zone difference in Pacific Daylight Time, which is GMT-7. At 12:00 p.m. PDT on August 1st, when the user accesses the record, the following formula is executed:

Date_Time_c - NOW()

In this computation, the function NOW() is utilized with a timestamp of 2024–08–01 19:00:00Z. It is then subtracted from another timestamp, 2024–08–02 07:00:00Z, resulting in the expected outcome of 0.5 (representing a duration of 12 hours).

Suppose that instead of NOW(), the formula converts the string “2024–08–01 12:00:00” to a Date/Time value:

Date_Time_c - DATETIMEVALUE( "2024-08-01 12:00:00" )

In this case, DATETIMEVALUE( “2024–08–01 12:00:00” ) is 2024–08–01 12:00:00Z, and returns a result of 0.79167, or 19 hours.

You cannot determine a user’s time zone within a formula. If all users share the same time zone, adjust for the time difference between the user’s time zone and GMT in your conversions. However, handling Daylight Saving Time (DST) is complex due to its varying dates each year. For transactions involving conversions between Date/Time values and Text or Date values, we recommend using Apex.