You need to sign in to do that

Don't have an account?

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!

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!

NPMThis 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! If I undestand your requirement correctly somthing like this should work:

MID(TEXT( DateTime__c ), 12, 2)

NPM

2008-03-21 16:10:00Z

Just for further clarity the converted text DateTime looks like:

paul-lmidoes this work for 2 digit months and single digit hours as well?

NPM2008-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 It does because the the conversion to text is always of the form:

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

Adding the -5 makes the time zone adjustment.

paul-lmicaveat 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

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

I do not experience that:

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

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

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

NPM

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

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 )

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

NPMGreat - I think we all learned a lot today :smileyvery-happy:
paul-lmiagreed, DLS is TERRIBLE :)

UTC for life :)

gireeshzjust wanted to say thanks to all for posting this. I needed this exact formula and was happy to see it out here already.

Cheers!