3

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?

5 Answers 5

3

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.

2
  • Follow up question... Is there a way to convert the time (which is GMT, I assume) into local time? Didn't see any obvious functions when creating a formula. Jul 9, 2014 at 17:24
  • All you're really doing here is string manipulation (which is why it's not a great approach). There is no time context involved, so you won't be able to do any easy time conversion. All you can do is locate the hour part and change the string value. This will be quite complex as you need to cater for when the conversion goes over the 24 hour mark.
    – Nick Cook
    Jul 10, 2014 at 1:07
7

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.

3
  • Hi Ever thing works fine but the time difference is exact five hours is it possible to add five hours to above formula.Because the received one is text right.Please help me @Jenny B
    – Pavan tej
    Mar 14, 2016 at 14:01
  • @Pavantej please see if this article helps help.salesforce.com/apex/…
    – Jenny B
    Mar 14, 2016 at 14:14
  • 1
    updated link to the blog post on one way to get time zones
    – gorav
    Nov 23, 2020 at 22:11
2

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.

1

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

0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .