Convenient time-related formulas in Salesforce

Obtaining the current time

The current time can be obtained using the TIMENOW function.

TIMENOW()

Adding to or subtracting from time

Time data type additions and subtractions are made in millisecond units.

Take care of the units when you want to obtain a time h hours later, m minutes later, s seconds later or ms milliseconds later.

Time data type + ( ( h * 60 + m ) * 60 + s ) * 1000 + ms

Calculating hours, minutes, seconds and milliseconds from the time

The HOUR function, MINUTE function, SECOND function and MILLISECOND function are used to calculate hours, minutes, seconds and milliseconds from the time.

The following format is used to obtain the hours.

HOUR( time datatype )

The following format is used to obtain the minutes.

MINUTE( time data type)

The following format is used to obtain the seconds.

SECOND( time data type)

The following format is used to obtain the milliseconds.

MILLISECOND( time data type)

Calculating the time from hours, minutes, seconds and milliseconds

The TIMEVALUE function is used to obtain the time from hours, minutes, seconds and milliseconds.

The TIMEVALUE function passes the hours, minutes, seconds and milliseconds as a string.

TIMEVALUE( "HH:MM:SS.SSS" )

If the hours, minutes, seconds and milliseconds are obtained as a number, this must be converted to a string using the TEXT function.

TIMEVALUE( TEXT( HH ) + ":" + TEXT( MM ) + ":" + TEXT( SS ) + "." + TEXT( SSS ) )

Calculating the time from milliseconds

The TIMEVALUE function is used to obtain the time from hours, minutes, seconds and milliseconds.

TIMEVALUE(
  TEXT( FLOOR( ms / 60 / 60 / 1000 ) )
  + ":" +
  TEXT( FLOOR( ms / 60 / 1000 ) - FLOOR( ms / 60 / 60 / 1000 ) * 60 )
  + ":" +
  TEXT( FLOOR( ms / 1000 ) - FLOOR( ms / 60 / 1000 ) * 60 )
  + "." +
  TEXT( ms - FLOOR( ms / 1000 ) * 1000 )
)

This can be represented concisely using the FORMATDURATION function.

Note that the FORMATDURATION function requires seconds as an argument and returns a value in the format `HH:MM:SS`.

TIMEVALUE( FORMATDURATION( FLOOR( ms / 1000 ) ) + "." + TEXT( ms - FLOOR( ms / 1000 ) * 1000 ) )

Calculating time differences

Since time differences are calculated in milliseconds, a coefficient must be applied when unit adjustments are required.

The following format is used to obtain it in millisecond units.

( time data type - time data type)

The following format is used to obtain it in second units.

( time data type - time data type) / 1000

The following format is used to obtain it in minute units.

( time data type - time data type) / 60 / 1000

The following format is used to obtain it in hour units.

( time data type - time data type) / 60 / 60 / 1000

Obtaining the time difference in the time data type is described using the TIMEVALUE function.

TIMEVALUE(
  TEXT( FLOOR( ( time data type - time data type) / 60 / 60 / 1000 ) )
  + ":" +
  TEXT( FLOOR( ( time data type - time data type) / 60 / 1000 ) - FLOOR( ( time data type - time data type) / 60 / 60 / 1000 ) * 60 )
  + ":" +
  TEXT( FLOOR( ( time data type - time data type) / 1000 ) - FLOOR( ( time data type - time data type) / 60 / 1000 ) * 60 )
  + "." +
  TEXT( ( time data type - time data type) - FLOOR( ( time data type - time data type) / 1000 ) * 1000 )
)

Since this is similar to calculating time from milliseconds, it can be written concisely using the FORMATDURATION function.

TIMEVALUE( FORMATDURATION( FLOOR( ( time data type - time data type) / 1000 ) ) + "." + TEXT( ( time data type - time data type) - FLOOR( ( time data type - time data type) / 1000 ) * 1000 ) )