Extract Time (Hour/Minutes) and AM/PM from DateTime field - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Christopher DeFilippiChristopher DeFilippi 

Extract Time (Hour/Minutes) and AM/PM from DateTime field

Hello,

I would like a formula field that extracts the hour/minutes and AM/PM from a DATETIME field. The time cannot be in military.

Thanks!
Best Answer chosen by Christopher DeFilippi
Ranu BariRanu Bari
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" )

All Answers

Ranu BariRanu Bari
Christopher,
All date-time values are stored in the database in GMT. To return the time as a string in “HH:MM:SS A/PM” format, use the following formula where TZoffset is the offset (in hours) from GMT:

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

Hope that helps :)
Christopher DeFilippiChristopher DeFilippi
I seem to be getting an error. Is there a way to not include seconds in the field? IF( OR( VALUE( MID( TEXT( Date_and_Time__c - 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" )
Ranu BariRanu Bari
What error you are getting? Can you put here, also let me know what formula you are putting, provide same here.
Christopher DeFilippiChristopher DeFilippi
User-added image
Christopher DeFilippiChristopher DeFilippi
Error: Field TZoffset does not exist. Check spelling.
Formula Field: Text
Ranu BariRanu Bari
TZoffset is the difference between the user’s time zone and GMT. So Please set that value first.
Christopher DeFilippiChristopher DeFilippi
Great. I adjusted. How do I remove the seconds?
Ranu BariRanu Bari
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" )
This was selected as the best answer
Christopher DeFilippiChristopher DeFilippi
Last question Ranu, when dealing with Daylight Savings. IE the Date/Time field says 11/6/2015 9:00 AM and the formula pulls 10:00 AM
Ranu BariRanu Bari
Christopher,
All Datetime field values are stored in GMT internally. They are then adjusted automatically (on std page laouts) for display to match the users Timezone.
This is so that there is one uniform Timezone effective in the database layer and the (notional) controller adjusts this value to the users Timezone to display in the view (MVC)
So if you saved a record while DST was effective, it would display as that, but internally it would still store as GMT. (i.e. subtract one hour in addition to applying the Timezone correction to GMT)
Eg if I created a record at 9 AM in July (when DST is in use, i.e. GMT + 1) with the user timezone as United Kingdom, it would store 8 AM in the CreatedDate because it corrects to GMT by subtracting an hour.
If however I created that record in January when the UK is GMT, at 9 AM, the CreatedDate would store 9 AM, because that is the true GMT time.
If you want to store a time in GMT without the platform applying a correction, suffix the Datetime with Z, the Zulu, which tells it that this is the time in GMT. Eg 2012-11-15 T03:31.00.000Z
Christopher DeFilippiChristopher DeFilippi
So essentially there is no way to use LEFT to get the time out of the formula ? There will always be this issue?
Scott DavisScott Davis
This is great, however i am still getting the TZoffset error that Christopher D. was getting.  When i tried chnaging TZoffset to -5 (for EST) in the formula, it still gives me the GMT time.  Am i missing something here?
John SmithJohn Smith
I've found this formula in a few different spots, so I think I must have something simple wrong. I'm in CST, which is GMT - 6, so I've updated the TZOffset to subtract 6 in my formula, but the result is still showing in GMT. 

What am I missing?

IF(
  OR(
    VALUE( MID( TEXT( Visit_Timestamp__c - 6 ), 12, 2 ) ) = 0,
    VALUE( MID( TEXT( Visit_Timestamp__c - 6 ), 12, 2 ) ) = 12
  ),
  "12",
  TEXT( VALUE( MID( TEXT( Visit_Timestamp__c - 6  ), 12, 2 ) ) 
   -
   IF( 
     VALUE( MID( TEXT( Visit_Timestamp__c - 6  ), 12, 2 ) ) < 12, 
     0, 
     12 
   ) 
  )
)
& ":" &
MID( TEXT( Visit_Timestamp__c - 6  ), 15, 2 )
& ":" &
MID( TEXT( Visit_Timestamp__c - 6  ), 18, 2 )
& " " &
IF(
  VALUE( MID( TEXT( Visit_Timestamp__c - 6 ), 12, 2 ) ) < 12,
  "AM",
  "PM"
)
Jacob NickelJacob Nickel
To fix the "subtracting the TZOffset value" issue please refer to this knowledge article (https://help.salesforce.com/articleView?id=000221466&type=1). Subtract N hours from a date time field: Datetimefield__c - (N/24) .
Sam LeeSam Lee
Thanks Ranu.  Do you have the same for 24 hour format?
Scott DavisScott Davis
Hi, I was dealing with the same problem. This is what i did: With formulas you need to determine DST for the formula to determine the correct time at all points of the year. So i first created a formula (checkbox) field names DST with the following formula: AND( datevalue(ActivityDateTime__c) >= DATE(YEAR(Datevalue(ActivityDateTime__c)),3,8) + CASE(MOD(DATE(YEAR(Datevalue(ActivityDateTime__c)),3,8) - DATE(1900,1,7),7), 0, 0, 1, 6, 2, 5, 3, 4, 4, 3, 5, 2, 1), datevalue(ActivityDateTime__c) < DATE(YEAR(Datevalue(ActivityDateTime__c)),11,1) + CASE(MOD(DATE(YEAR(Datevalue(ActivityDateTime__c)),11,1) - DATE(1900,1,7),7), 0, 0, 1, 6, 2, 5, 3, 4, 4, 3, 5, 2, 1)) Then i used that field along with the original DATETIME field to create a formula that extracts the proper time with the following formula: IF( DST__c = false, IF( OR( VALUE( MID( TEXT( ActivityDateTime__c - (5/24)), 12, 2 ) ) = 0, VALUE( MID( TEXT( ActivityDateTime__c - (5/24) ), 12, 2 ) ) = 12 ), "12", TEXT( VALUE( MID( TEXT( ActivityDateTime__c - (5/24) ), 12, 2 ) ) - IF( VALUE( MID( TEXT( ActivityDateTime__c - (5/24) ), 12, 2 ) ) < 12, 0, 12 ) ) ) & ":" & MID( TEXT( ActivityDateTime__c - (5/24) ), 15, 2 ) & " " & IF( VALUE( MID( TEXT( ActivityDateTime__c - (5/24) ), 12, 2 ) ) < 12, "AM", "PM" ), IF( OR( VALUE( MID( TEXT( ActivityDateTime__c - (4/24)), 12, 2 ) ) = 0, VALUE( MID( TEXT( ActivityDateTime__c - (4/24) ), 12, 2 ) ) = 12 ), "12", TEXT( VALUE( MID( TEXT( ActivityDateTime__c - (4/24) ), 12, 2 ) ) - IF( VALUE( MID( TEXT( ActivityDateTime__c - (4/24) ), 12, 2 ) ) < 12, 0, 12 ) ) ) & ":" & MID( TEXT( ActivityDateTime__c - (4/24) ), 15, 2 ) & " " & IF( VALUE( MID( TEXT( ActivityDateTime__c - (4/24) ), 12, 2 ) ) < 12, "AM", "PM" )) Legend: (5/24) & (4/24) --> Time zone calculations for eastern time. You will need to change this based on your timezone. DST__c --> The DST formula field that i created initially ActivityDateTime__c --> the original DATETIME field Hopefully this works for you.
Jacob NickelJacob Nickel
@Sam This is the formula I was using to create a formulat field that displays the hour of a DATE\TIME field in 24 hour format: 

TEXT((NOW() - CreatedDate) - N) 
*N equlas the value from the formula below.

Get the UTC Offset of your Timezone and apply the following calculations to it. (In this example we use Mountain Time)
-7 * 60 minutes = -420 minutes
-420/1440 minutes in a 24 hour period = -0.2916

The resulting formula modification would be:
TEXT((NOW() - DATE) - 0.2916)
Matt GoldMatt Gold
Perfect! Thank you Ranu!
Nicholas ZozayaNicholas Zozaya
Here's a solution that accounts for timezone + Standard Time derived from a date/time field. NOTE: This doesn't account for DST, that formula was too long for SF to handle, so you'd have to adjust this for DST by adding 1 to the "-7", "-5", & "-4" portions so that they'll be "-8", "-5", "-4". To do this I made a Timezone__c field on Owner. You'll want to substitute "Tour_Scheduled_Date_Time__c" with the Date/Time you're trying to parse. 

IF(ISPICKVAL(Owner.Timezone__c , "PST"),     
       IF(
        VALUE(MID(TEXT(Tour_Scheduled_Date_Time__c +((1/24)*-7)),12,2))>=12,
         IF(VALUE(MID(TEXT(Tour_Scheduled_Date_Time__c +
         ((1/24)*-7)),12,2)) = 12, "12",
         TEXT(VALUE(MID(TEXT( Tour_Scheduled_Date_Time__c +
         ((1/24)*-7)),12,2))-12)), 
        MID(TEXT(Tour_Scheduled_Date_Time__c +((1/24)*-7)),12,2))
        +
        ":"
        +
        MID(TEXT(Tour_Scheduled_Date_Time__c ),15,2)
        +
        IF(
        VALUE(MID(TEXT(Tour_Scheduled_Date_Time__c +
        ((1/24)*-7)),12,2))>=12, " PM", 
        " AM"), 
IF(ISPICKVAL(Owner.Timezone__c, "EST"),
       IF(
        VALUE(MID(TEXT(Tour_Scheduled_Date_Time__c +((1/24)*-4)),12,2))>=12, 
        IF(VALUE(MID(TEXT(Tour_Scheduled_Date_Time__c +
         ((1/24)*-4)),12,2)) = 12, "12",
         TEXT(VALUE(MID(TEXT( Tour_Scheduled_Date_Time__c +
         ((1/24)*-4)),12,2))-12)), 
        MID(TEXT(Tour_Scheduled_Date_Time__c +((1/24)*-4)),12,2))
        +
        ":"
        +
        MID(TEXT(Tour_Scheduled_Date_Time__c ),15,2)
        +
        IF(
        VALUE(MID(TEXT(Tour_Scheduled_Date_Time__c +
        ((1/24)*-4)),12,2))>=12, " PM", 
        " AM"),
IF(ISPICKVAL(Owner.Timezone__c, "CST"),
       IF(
        VALUE(MID(TEXT(Tour_Scheduled_Date_Time__c +((1/24)*-5)),12,2))>=12, 
        IF(VALUE(MID(TEXT(Tour_Scheduled_Date_Time__c +
         ((1/24)*-5)),12,2)) = 12, "12",
         TEXT(VALUE(MID(TEXT( Tour_Scheduled_Date_Time__c +
         ((1/24)*-5)),12,2))-12)), 
        MID(TEXT(Tour_Scheduled_Date_Time__c +((1/24)*-5)),12,2))
        +
        ":"
        +
        MID(TEXT(Tour_Scheduled_Date_Time__c ),15,2)
        +
        IF(
        VALUE(MID(TEXT(Tour_Scheduled_Date_Time__c +
        ((1/24)*-5)),12,2))>=12, " PM", 
        " AM"),
"")))
Jeffrey BennJeffrey Benn
That solution from Scott Davis is delightful.  Thanks!
Harshal GosaviHarshal Gosavi
I have a custom field with DataType is TIME. I want to remove AM and PM from the Field. How can I do this?
smitha vikramsmitha vikram
how do i calculate the tzoffset

so essentialy i am trying to convert 7/24/2018 3:56 PM to two separate columns 1) date 2) time in hh:mm 12 hr format?
Denis YanovDenis Yanov
If you track time on tasks in Salesforce with TMetric time tracker (https://tmetric.com/integrations/salesforce-time-tracking/) fully integrated with Salesforce it will remove the marking for AM ans PM hours and, it also automatically calculates time on all the entries and in case you work, from, for example, 23.00 to 2.00, it will distribute the time on two calendar days to avoid any confusion.
Harold CarlsonHarold Carlson
Found this topic and only need to extract the hours and display them in 24-hour format.  

I pulled out what I needed from Scott's formula above and was left with.  
IF( Activity_Date_Time_DST__c = false, VALUE( MID( TEXT( ActivityDateTime - (8/24)), 12, 2 ) ),  VALUE( MID( TEXT( ActivityDateTime - (7/24)), 12, 2 ) ) )
Refer to Scott's formula for DST (Activity_Date_Time_DST__c in the above formula).  
 
Tom NollTom Noll
Apologies if I missed this in the thread, but is it possible for the output of this be a time datatype instead of text?

I used the above method to create a text field showing the Start Time of an event in "HH:MM AM/PM" format.

Is there a way to convert this format to a time datatype? I don't mind creating a new field to do this rather than trying to incorporate it in the formula at the above thread. But this would be super helpful. Thanks!
Sandipan GhoshSandipan Ghosh
I tried this formula as DST  and it worked. 


However we are hardcoding the start date for EDT and EST. Is there any way we can pull the start date for EST and EDT from the system because every year the EDT and EST start dates will change.

AND(
DATEVALUE(CreatedDate) >= DATE(YEAR(DATEVALUE(CreatedDate)),3,8) + CASE(MOD(DATE(YEAR(DATEVALUE(CreatedDate)),3,8) - DATE(1900,1,7),7), 0, 0, 1, 6, 2, 5, 3, 4, 4, 3, 5, 2, 1),
DATEVALUE(CreatedDate) < DATE(YEAR(DATEVALUE(CreatedDate)),11,1) + CASE(MOD(DATE(YEAR(DATEVALUE(CreatedDate)),11,1) - DATE(1900,1,7),7), 0, 0, 1, 6, 2, 5, 3, 4, 4, 3, 5, 2, 1)
)

If you see here the 3,8 is March 8th when EDT starts.

11,1 is the Daylight savings time which starts 11.1.2020. Is there a way we can avoid updating the date field every year?

 
Kimberly WargoKimberly Wargo
Ok Im ready to jump off a bridge on this one It shouldn't be that difficult...
Here is my formula:
IF( OR( VALUE( MID( TEXT( Date_Time_Received__c - 4  ), 12, 2 ) ) = 0, VALUE( MID( TEXT( Date_Time_Received__c - 4 ), 12, 2 ) ) = 12 ), "12", TEXT( VALUE( MID( TEXT( Date_Time_Received__c - 0 ), 12, 2 ) ) - IF( VALUE( MID( TEXT( Date_Time_Received__c - 4 ), 12, 2 ) ) < 12, 0, 12 ) ) ) & ":" & MID( TEXT( Date_Time_Received__c - 4  ), 15, 2 ) & ":" & MID( TEXT( Date_Time_Received__c - 4 ), 18, 2 ) & " " & IF( VALUE( MID( TEXT( Date_Time_Received__c - 4 ), 12, 2 ) ) < 12, "AM", "PM" )

Works fine but the time it is returning is wrong. The time stamp is 1:37 PM but it is returning 5:37 PM.
I've changed the variation form - 4 to - 5 to - 0 and it retunrs the same time of 5:37 PM.
What am I doing worng? I've spent way too much time on this.. Im on EST time FYI
Thanks for any and all help
 
Sandipan GhoshSandipan Ghosh

Did you check the option called, "Improve DateValue". Also please go through the latest maintenance winter 20 certs and you probably can try changing the value to DATE VALUE to consider the DST.

https://trailhead.salesforce.com/en/content/learn/modules/platform-app-builder-certification-maintenance-winter-20/learn-whats-new-for-app-builders-in-winter-20

Peter McDowellPeter McDowell
Kimberly and others getting the TZoffset issue that results in GMT time every time. If you replace the TZoffset with (4/24), it works for EST. So, wherever you now have "- 4", replace it with "- (4/24)" and it should work.