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?

2

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.

  • 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. – AngryHacker Jul 9 '14 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 '14 at 1:07
5

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.

  • 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 '16 at 14:01
  • @Pavantej please see if this article helps help.salesforce.com/apex/… – Jenny B Mar 14 '16 at 14:14
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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.