Useful datetime-related formulas in Salesforce

Get the current date and time

The current date and time can be obtained with the NOW function.

NOW()

Add/subtract date and time

The datetime type has an integer part representing the date and a decimal part representing the time.

If you wish to get the date and time after d days, h hours, m minutes, s seconds, or ms milliseconds, add the units with care.

Datetime type + d + ( h + ( m + ( s + ms / 1000 ) / 60 ) / 60 ) / 24

The ADDMONTHS function is used for adding and subtracting the number of months.

ADDMONTHS ( datetime type , n )

Addition or subtraction in years is 12 times the addition or subtraction of the number of months.

ADDMONTHS( datetime type , n * 12 )

Get date from datetime

Use the DATEVALUE function to obtain the date portion from a datetime type.

DATEVALUE( datetime type )

Get time from datetime

Use the TIMEVALUE function to obtain the time portion from a datetime type.

TIMEVALUE( datetime type )

Calculate date and time from year, month, hour, minute, second, millisecond

Use the DATETIMEVALUE function to obtain the date and time from the year, month, day, hour, minute, and second.

DATETIMEVALUE( "YYYY-MM-DD HH:MM:SS" )

As the DATETIMEVALUE function cannot add millisecond information, a millisecond value must be added.

DATETIMEVALUE( "YYYY-MM-DD HH:MM:SS" ) + MS / 24 / 60 / 60 / 1000

Calculate datetime from date and time

When a date and time are present, it is simpler to calculate the value by dropping the milliseconds rather than extracting individual components like year, month, or second.

DATETIMEVALUE( TEXT( date type ) + " " + LEFT( TEXT( time type ) , 8 ) ) + MILLISECOND( time type ) / 24 / 60 / 60 / 1000

Another way is to add the time separately, utilizing the fact that passing only the date type to the DATETIMEVALUE function results in ‘DateT00:00:00.000Z’.

DATETIMEVALUE( date type ) + ( HOUR( time type ) + ( MINUTE( time type ) + ( SECOND( time type ) + MILLISECOND( time type ) / 1000 ) / 60 ) / 60 ) / 24

Calculate the difference between the datetime

Since the difference between datetime is calculated with the integer part being the date and the decimal part being the time, a factor must be applied if units need to be adjusted.

The following format is used to obtain the data in day units.

( datetime type - datetime type )

The following format is used to obtain the data in hour units.

( datetime type - datetime type ) * 24

The following format is used to obtain the data in minute units.

( datetime type - datetime type ) * 24 * 60

The following format is used to obtain the data in second units.

( datetime type - datetime type ) * 24 * 60 * 60

The following format is used to obtain the data in millisecond units.

( datetime type - datetime type ) * 24 * 60 * 60 * 1000

To obtain the difference in a time type of less than 24 hours, write it in the form of using the TIMEVALUE function.

TIMEVALUE(

  TEXT( FLOOR( ( TIMEVALUE( datetime type ) - TIMEVALUE( datetime type ) ) / 60 / 60 / 1000 ) )

  + ":" +

  TEXT( FLOOR( ( ( TIMEVALUE( datetime type ) - TIMEVALUE( datetime type ) ) / 60 / 1000 ) - FLOOR( ( ( TIMEVALUE( datetime type ) - TIMEVALUE( datetime type ) ) / 60 / 60 / 1000 ) * 60 )

  + ":" +

  TEXT( FLOOR( ( ( TIMEVALUE( datetime type ) - TIMEVALUE( datetime type ) ) / 1000 ) - FLOOR( ( ( TIMEVALUE( datetime type ) - TIMEVALUE( datetime type ) ) / 60 / 1000 ) * 60 )

  + "." +

  TEXT( ( TIMEVALUE( datetime type ) - TIMEVALUE( datetime type ) ) - FLOOR( ( TIMEVALUE( datetime type ) - TIMEVALUE( datetime type ) ) / 1000 ) * 1000 )

)

Use the FORMATDURATION function to obtain the difference as a string with the number of days included.

FORMATDURATION( datetime type , datetime type )

Contributor: Hiroki Nakazawa | Trailblazer Community Forum Ambassador

Currently, he is in charge of Salesforce function development and maintenance, focusing on Apex and LWC, as a developer at circlace Inc.
He is also involved in the management of several communities in addition to answering questions in the Trailblazer Community.

The posts represent the views of the author who submitted them alone and do not necessarily reflect the position of Salesforce. Regardless of whether or not associated with Salesforce, posts may contain or constitute products, services, information, data, content or other materials provided by third parties ("Third Party Materials"). Salesforce does not control, endorse, or guarantee any Third Party Materials (including their accuracy, validity, timeliness, completeness, reliability, integrity, quality, legality, usefulness, security, or applicable intellectual property rights) and assumes no responsibility therefor. Posts provided in response to questions posted via message boards or forums, or that claim to answer questions, including questions about Salesforce or programs, are provided for general informational purposes and should not be relied upon as answers to specific questions not (even if the answer is marked as the "best" answer or similarly qualified). You should always contact Salesforce support for answers to your specific questions. Salesforce has no control over the content of posts and assumes no responsibility for the use or misuse (including distribution) of the content of posts by third parties.

If you have any questions, please take advantage of the insights of our Trailblazer community.
https://trailhead.salesforce.com/trailblazer-community/feed