ShowAll Questionssorted byDate Posted
paul-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!
NPM

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!
NPM

Just for further clarity the converted text DateTime looks like:

 2008-03-21 16:10:00Z

paul-lmi
does this work for 2 digit months and single digit hours as well?
NPM

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-lmi
perfect.  so the only caveat is during daylight savings time, which i'll figure out.  thank you so much :)
NPM

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

paul-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.
NPM

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-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
NPM

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

NPM

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-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.
NPM
Great - I think we all learned a lot today :smileyvery-happy:
paul-lmi
agreed, DLS is TERRIBLE :)

UTC for life :)
gireeshz
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!