function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
paul-lmipaul-lmi 

formula to return the hour from a date/time?

i'm stuck on this one.  we want to be able to report on trending for cases/calls on a per hour basis.  typically, one would do this by extracting the hour of the day from a date/time or time field and then summary reporting on that.

since the text functions for formula fields don't include a substring capability, i'm getting lost here.

does anyone have or know how to create a formula that will take a date/time and return the hour from it?  preferably 24 hour scale vs. 12 hour scale...

thanks!
NPMNPM

If I undestand your requirement correctly somthing like this should work:

MID(TEXT(   DateTime__c  ), 12, 2)

This converts a Date/Time field to Text then extracts the hour.
 
This was tested using a text formula field named Hour that used the above formula and gave the following result:
DateTime
3/21/2008 11:51 AM
Hour
15
 
Note: when the DateTime is converted to Text it converts to Z time, which explains the hour being 15 in this example.  If that is an issue you may need to do another formula field that adjusts the hour to local.
In this above case:
VALUE( Hour__c ) -5
gives
LocalHour
10
 
This second formula is of type Number
 
Hope this all helps!
NPMNPM

Just for further clarity the converted text DateTime looks like:

2008-03-21 16:10:00Z
paul-lmipaul-lmi
does this work for 2 digit months and single digit hours as well?
NPMNPM

It does because the the conversion to text is always of the form:

2008-03-25 08:25:00Z   This is the representation of 3/25/2008 4:25 AM
 
2008-12-25 05:29:00Z   This is the representation of 12/25/2008 12:29 AM
paul-lmipaul-lmi
perfect.  so the only caveat is during daylight savings time, which i'll figure out.  thank you so much :)
NPMNPM

Great - I have found that rather than 2 formulas you can combine the functions in one:

VALUE( MID(TEXT( DateTime__c ), 12, 2) )

This is a number formula field that gives the hour (at Z).

VALUE( MID(TEXT( DateTime__c ), 12, 2) ) -5

Adding the -5 makes the time zone adjustment.

 
paul-lmipaul-lmi
caveat to doing the calculation of time zone, if it's say 3AM, you'll get a negative number as a result when subtracting the offset.
NPMNPM

I do not experience that:

DateTime
12/25/2008 12:29 AM
  
Text Datetime
2008-12-25 05:29:00Z
  
ZHour
05
  
LocalHour
0

Using VALUE( MID(TEXT( DateTime__c ), 12, 2) ) -5 to calculate local time.  I guess I would if my offset was greater than 5, but it should only be what the actual offset from Z is.
paul-lmipaul-lmi
try it with your Zhour as an hour less than 5.  sorry, in my other post I meant 3 AM Zhour, not local, so that'd be 10 PM local if using Zhour -5
NPMNPM

Aha - you are right.  Thanks for pointing it out.  It would probably take some crazy IF statments to make the adjustment conditional.

DateTime
12/25/2008 10:29 PM
  
Text Datetime
2008-12-26 03:29:00Z
  
ZHour
03
  
LocalHour
-2
NPMNPM

Does this check out?

IF(VALUE( MID(TEXT( DateTime__c ), 12, 2) ) >= 5, VALUE( MID(TEXT( DateTime__c ), 12, 2) ) -5 , VALUE( MID(TEXT( DateTime__c ), 12, 2) ) +19 )

 

DateTime
12/25/2008 10:29 PM
  
Text Datetime
2008-12-26 03:29:00Z
  
ZHour
03
  
LocalHour
22
paul-lmipaul-lmi
i did it this way

if((VALUE( MID(TEXT(  CreatedDate  ), 12, 2) ) - 4) <= 0, (VALUE( MID(TEXT(  CreatedDate  ), 12, 2) )) - 4 + 24, (VALUE( MID(TEXT(  CreatedDate  ), 12, 2) )) - 4 )

basically
if hour <= 0 subtract DLS and add 24, otherwise subtract DLS

the only thing that this doesn't take into account is that DLS changes THIS MONTH, so if a report using this field spans both time frames, there will be a little overlap.  i'm ok with that though.
NPMNPM
Great - I think we all learned a lot today :smileyvery-happy:
paul-lmipaul-lmi
agreed, DLS is TERRIBLE :)

UTC for life :)
gireeshzgireeshz
just wanted to say thanks to all for posting this. I needed this exact formula and was happy to see it out here already.

Cheers!