How to extract the time into a formula field?

Question

I want to extract the time component of CreatedDate (standard field) into a formula field (which should be a string) that contains just the time part.

So if CreatedDate is Jan 4, 2014 17:23:10, then then formula field should just have 17:23:10.

While setting up the formula field, I've tried the following:

CreatedDate.format('hh:mm:ss') DateValue(CreatedDate).format('hh:mm:ss')

but nothing appears to work.

Am I missing something simple?

Answer 1

I'm not sure where you're getting those formula operators from.

For a list of actual operators you can use, see the help docs at: https://help.salesforce.com/apex/HTViewHelpDoc?id=customize_functions.htm

In regards to your question, the following formula works for me:

LEFT(RIGHT(text( CreatedDate ),9),8)

I don't know the impacts of different regions will have on this.

I also get the feeling this may not be the best way to capture.

Answer 2

There is an example on the help docs for formula date examples that shows the following to get the time from a Date/Time field

Finding the Hour, Minute, or Second from a Date/Time
To get the hour, minute, and second from a Date/Time field as a numerical value, use the following formulas where TZoffset is the difference between the user’s time zone and GMT.
For hour in 24–hour format:

VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) )

For hour in 12–hour format:

IF(
  OR(
    VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) = 0,
    VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) = 12
  ),
  12,
  VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) 
   - 
   IF( 
     VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) < 12, 
     0, 
     12 
   )
)

For minutes:

VALUE( MID( TEXT( date/time - TZoffset ), 15, 2 ) )

For seconds:

VALUE( MID( TEXT( date/time - TZoffset ), 18, 2 ) )

And, to get “AM”. or “PM” as a string, use:

IF(
  VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) < 12,
  "AM",
  "PM"
)

To return the time as a string in “HH:MM:SS A/PM” format, use the following formula:

IF(
  OR(
    VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) = 0,
    VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) = 12
  ),
  "12",
  TEXT( VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) 
   -
   IF( 
     VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) < 12, 
     0, 
     12 
   ) 
  )
)
& ":" &
MID( TEXT( date/time - TZoffset ), 15, 2 )
& ":" &
MID( TEXT( date/time - TZoffset ), 18, 2 )
& " " &
IF(
  VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) < 12,
  "AM",
  "PM"
)

When working with time in formula fields, you need to consider the time difference between your organization and GMT. See A Note About Date/Time and Time Zones for help understanding the time zone offset used in this formula.

Here is a blog post on one way to get time zones.

Answer 3

There isn't any formatting function for date/time fields, nor for extracting the time.

You can convert the date/time value to a string using TEXT( CreatedDate ), which will return the string in the format "YYYY-MM-DD HH:MM:SS", and then trim off the date part, but you have to be wary of time zones. Normally the date/time is converted from GMT to the user's time zone when viewed, but wrapping it in TEXT() will cause the GMT value to be returned, not the converted time zone. So you would need to convert it to the user's time zone first, which can't be determined in a formula. If all your users are in the same time zone, you can adjust the time zone difference by adding/subtracting the time difference between the user's time zone and GMT, but Daylight Saving Time makes this challenging too.

See "Overview of Using Date and Date/Time Values in Formulas" in the Salesforce Help, particularly the "A Note About Date/Time and Time Zones" section.

Answer 4

Here's a KB Article to Convert DateTime from GMT to Local and automaically adjust for GMT

Knowledge Article Number: 000181642

Description This formula will allow you to convert any Date/Time field to display it's value in another time zone. It will convert the Date (if necessary), the Time, and will account for Daylight Savings (using US start/end dates for DST).

Answer 5

To display only the time, use:

LEFT(TEXT(TIMEVALUE(CreatedDate)),8)

This converts the TimeValue (“HH:MM:SS.MS”) of the DateTime field to text then uses just the leftmost 8 characters.​​​​​​​