+ Start a Discussion
JWOODYJWOODY 

Getting the hour from Date/Time field

Hi,
 
I am trying to get the hour of the 'Date/Time Opened' field within Cases.  Ideally I'd like to be able to pull something like 4:00 PM or 20:00, or something along these lines.
 
Anybody know a way to do this with a formula?
 
Thanks!!

Message Edited by JWOODY on 10-18-2006 09:25 PM

EricBEricB
That is pretty challenging to do at the moment, without a pretty aggregious hack.

In Winter '07, the TEXT() function will allow you to convert a datetime field to its text representation.  That will make it a lot easier to parse out the time portion of a datetime field using the MID() function.

Something like this:

Time Portion =
MID ( TEXT (My_Datetime__c ), 12, 5)

Cheers,
Eric
MikeWiresMikeWires
I was searching for the same solution and with the long awaited Winter 07' Release, you can pull out the current hour. I just put this together and haven't finish the whole project, however the plan is to use this to create a much more complex formula looking at state information for the creation of dynamic lists based upon timezones.

VALUE(MID(TEXT(NOW()),12,2)) - (Hours from GMT)

Current Hour if you're EST (Number)
VALUE(MID(TEXT(NOW()),12,2)) - 5

This of course breaks down after 7PM EST, as the formula would go negative. The addition of a negative check (if possible) or simply a > check on the GMT time could work as a fix.
MicaiahFMicaiahF
Thanks for the post!  This really helped. 

Here is a formula I created with yours as the base.  This formula reads the Last Modfied Date and returns which Shift it was modfied on in PST. 

(CASE(VALUE(MID(TEXT( LastModifiedDate),12,2)),
0, "Swing",
1,"Swing",
2,"Swing",
3,"Swing",
4,"Swing",
5,"Swing",
6,"Swing",
7,"Night",
8,"Night",
9,"Night",
10,"Night",
11,"Night",
12,"Night",
13,"Night",
14,"Night",
15,"Day",
16,"Day",
17,"Day",
18,"Day",
19,"Day",
20,"Day",
21,"Day",
22,"Day",
23,"Day",
"The formula did not calculate correctly. please inform the system admin."))

Best,
Micaiah

MikeWiresMikeWires
Glad that helped! Here's the completed project I put together it might be of use to someone. Although I imagine it could be done with Apex much easier.... hmm

Premise: Analyze current time, analyze timezone based upon state, analyze "call at night" checkbox in order to determine if an individual is available to be called.

Output: 3 Formula fields utilized by a "Available To Call" view.

Requirement: 5 Custom Formula Fields, 1 Custom Check Box Field, (1 Custom Date Field: optional)

Formulas
GAF1_CurrentTime // This handles GMT time adjustment for East Coast Time

CASE( VALUE(MID(TEXT(NOW()),12,2)) ,
0, 19,
1, 20,
2, 21,
3, 22,
4, 23,
5, 0,
6, 1,
7, 2,
8, 3,
9,  4,
10,  5,
11,  6,
12,  7,
13,  8,
14,  9,
15,  10,
16,  11,
17,  12,
18,  13,
19,  14,
20,  15,
21,  16,
22,  17,
23,  18,
0)

GAF2_TimeZone // Timezone lookup

CASE(State,
"FL",1,
"GA",1,
"SC",1,
"NC",1,
"TN",1,
"KY",1,
"VA",1,
"WV",1,
"IN",1,
"OH",1,
"MI",1,
"MD",1,
"DE",1,
"NJ",1,
"PA",1,
"CT",1,
"NY",1,
"RI",1,
"MA",1,
"NY",1,
"VT",1,
"NH",1,
"ME",1,
"DC",1,
"PR",1,
"AL",2,
"MS",2,
"LA",2,
"TX",2,
"OK",2,
"AR",2,
"IL",2,
"MO",2,
"KS",2,
"NE",2,
"IA",2,
"WI",2,
"MN",2,
"SD",2,
"ND",2,
"NM",3,
"CO",3,
"UT",3,
"ID",3,
"WY",3,
"MT",3,
"CA",4,
"NV",4,
"OR",4,
"WA",4,
"AZ",4,
"HI",4,
2)

GAF3_StartCheck  // Available 'start time' for contacting the individual (9PM EST start)

IF(GAF1_CurrentTime__c >=  GAF2_TimeZone__c +8,1,0)

GAF4_StopCheck // Available 'stop time' for contacting the individual (9PM EST stop)

IF(GAF1_CurrentTime__c <  GAF2_TimeZone__c +20,1,0)

GAF5_CallAtNight // Call At Night Check Box + Night Time Calling Lock (Night = 5PM)

IF(
 OR(
  AND(
  Call_At_Night__c,
  GAF1_CurrentTime__c >=17
   ),
  AND(
   NOT(Call_At_Night__c),
    GAF1_CurrentTime__c <=17)),
 1,
0
)

Setup a list with the following criteria.

List-> Available To Be Called Records

GAF3_StartCheck equals 1
GAF4_StopCheck equals 1
GAF5_CallAtNight equals 1

I also utilize a "Call After Date" which is a date field users use to keep clients from being called "too early".

Call_After_Date less than equal to TODAY

My organization primary uses this with the AppExchange partner "GetAnswered" for automatic 1:1 dialing, however its quite useful within Views as well.


Mike
testortestor
Is anyone having trouble with the Text(My_Datetime__c) function?  I find that in the Tab layout and in Reports, the value for minutes always comes out to be :04

If I open any record and view its data, the Text(My_Datetime__c) function shows minutes equal to the minutes in the actual My_Datetime__c field.  It is just in the Tab layout and in Reports that the minutes changes to :04 and invalidates the field.

You can test this by creating a custom object with a  My_Datetime__c field and a Formula field using Text(My_Datetime__c).  Edit the "Search Layout" for the custom "Tab" to include "My Datetime" and the formula field for Text(My_Datetime__c). 

Create a record with any time not equal to 04.  Click the custom tab and compare the "My Datetime" value to the text version of that value.  You'll see that hours change just as described in the documentation for the TEXT function.  But I find that the minutes always change to 04!!!  Curious to know if others have this problem.
MLamb2005MLamb2005

Hey all,

This thread helped me solve a similar problem, so I thought I'd post my solution in case it would be of value to someone else.  I had a date/time field, and needed to extract the time in "hh:mm AM/PM" format.  The following formula will take something like 19:15:00Z from Salesforce and convert it to "2:15 PM" as text.  Note, the formula is setup for Central Standard Time.

IF( VALUE(MID(TEXT( Most_Recent_Start_AUTO__c ) , 12, 2)) > 17,      // Check if the hour value is past noon (12 + 5 for CST)

TEXT(VALUE(MID(TEXT( Most_Recent_Start_AUTO__c ) , 12, 2)) -17),     //If the hour value is past noon, subtract 17 to get it into CST

TEXT(VALUE(MID(TEXT( Most_Recent_Start_AUTO__c ), 12, 2)) -5) )     //If the hour value is noon or sooner, subtract 5 to get back into CST

& ":" & MID(TEXT( Most_Recent_Start_AUTO__c ) , 15, 2)      // Append ":" and the minute digits to the resulting hour digits

& IF(VALUE(MID(TEXT( Most_Recent_Start_AUTO__c ) , 12, 2)) >= 17, " PM", " AM")   // If the hour value is >= noon, append "PM".  If not, append "AM"

 

To use the formula, simply remove the comments to the right of each line.



Message Edited by MLamb2005 on 07-22-2008 02:17 PM
MLamb2005MLamb2005

I spoke too soon!  The formula posted above did not work for all times.  But the one below does, and it much shorter in the character count.

 

 //For space considerations, Case the hours digits as numbers and assign the new appropriate 12 hour clock value.  The above values work for CST, you'll need to shift accordingly
Text(CASE(VALUE(MID(TEXT( Most_Recent_Start_AUTO__c ) , 12, 2)) , 0, 7, 1, 8, 2, 9,3, 10,4, 11,5, 12,6, 1,7, 2,8, 3,9, 4,10, 5,11, 6,12, 7,13, 8,14,9,15, 10,16, 11,17, 12,18, 1,19, 2,20, 3,21, 4,22, 5,23, 6, 24))  

//Append the minutes
& ":" & MID(TEXT( Most_Recent_Start_AUTO__c ) , 15, 2)   

// If the hour is between 05 (12am) and 16 (11am), append " AM", otherwise append " PM"
& IF(AND(VALUE(MID(TEXT( Most_Recent_Start_AUTO__c ) , 12, 2)) >= 5, VALUE(MID(TEXT( Most_Recent_Start_AUTO__c ) , 12, 2)) <= 16), " AM", " PM") 


Remove the comment lines and replace Most_Recent_Start_AUTO__c with your date/time variable, should work fine.

Matt

jvolkovjvolkov

In case anybody needs this, subtracting 7 returns pacific military time, subtracting 4 returns eastern military time, ect. Assumes North American org of course.

 

 

VALUE( MID( TEXT( LastModifiedDate ), 12, 2 ) ) 
- 7 /*Make it Pacific Military Time*/

 

 

 

 

 

 

 

NattyForceNattyForce

Matt,

 

Thanks! That worked wonders.

 

I'm completely new to SF so forgive me for asking what may be a silly question condsidering the formula you've provided..

 

How would I alter your formula to get a two digit output for the hours? ie: I need 2:30 PM to display as 02:30 PM

 

Thanks again.

 

-Natty

Veeru AVeeru A

Matt.

I modified your example and it works except for midnight - e.g 00:01 AM  - Thoughts?

 

Text(CASE(VALUE(MID(TEXT( Date_Occurred__c ), 12, 2)), 
0, 8, 
1, 9, 
2, 10, 
3, 11, 
4, 12, 
5, 1, 
6, 2, 
7, 3, 
8, 4, 
9, 5, 
10, 6, 
11, 7, 
12, 8, 
13, 9, 
14,10, 
15, 11, 
16, 12, 
17, 1, 
18, 2, 
19, 3, 
20, 4, 
21, 5, 
22, 6, 
23, 7, 
24)) 
& ":" & MID(TEXT( Date_Occurred__c ) , 15, 2) 
& IF(AND(VALUE(MID(TEXT( Date_Occurred__c ) , 12, 2)) >= 4, VALUE(MID(TEXT( Date_Occurred__c ) , 12, 2)) <= 15), " AM", " PM")

Morvana WarringtonMorvana Warrington
Hi,

I solved it this way:--

Knowledge Article Number: 000004518


Description
How do we create a formula to return the hour from a date/time value?
Sample use case: For staffing purposes, we want to be able to report on our case creation volume broken down by Created Hour.

Resolution
You can create a formula using the following syntax to return the Time from a Date/Time value as a number from 0 to 23:

FLOOR ( MOD( CreatedDate - ($System.OriginDateTime),1) *24)

NOTES:
* The syntax above will return the time value in GMT. The value will not be offset by your local timezone or by Daylight Savings.
* "$System.OriginDateTime" represents 1/1/1900, 12:00 AM GMT. You may add or subtract a number of hours to offset this value to a specific timezone, to return the result based on that timezone rather than GMT. However, bear in mind that this still will not account for Daylight Savings.
For example:

To return a value in Eastern Standard Time (GMT-5) you would need to add 5 hours to "$System.OriginDateTime", like: FLOOR ( MOD( CreatedDate - ($System.OriginDateTime + 5/24),1) *24)

For AEST I did - 10 hours so the formula was:
FLOOR ( MOD( CreatedDate - ($System.OriginDateTime - 10/24),1) *24)

Fredric UribeFredric Uribe
Here's something simple i made for dealing with Daylight Savings Time..

IF(AND(MONTH(DATEVALUE(CreatedDate ))>2, MONTH(DATEVALUE(CreatedDate ))<11), VALUE(MID(TEXT(CreatedDate ),12,2))> 11, VALUE(MID(TEXT(CreatedDate ),12,2))> 12),

the true value is for times within daylight savings time. the false value is for times outside of daylight savings time.

Daylight saving time starts on the second Sunday in March and ends on the first Sunday in November in the USA. So this isn't exact, but will do the trick for all but a few days a year.
 
Stephanie Paone 11Stephanie Paone 11

@ DSchueler,

Do you have any suggestions as to how I can tweak this to capture PST insread of EST?

-------

Text(CASE(VALUE(MID(TEXT( Date_Occurred__c ), 12, 2)), 
0, 8, 
1, 9, 
2, 10, 
3, 11, 
4, 12, 
5, 1, 
6, 2, 
7, 3, 
8, 4, 
9, 5, 
10, 6, 
11, 7, 
12, 8, 
13, 9, 
14,10, 
15, 11, 
16, 12, 
17, 1, 
18, 2, 
19, 3, 
20, 4, 
21, 5, 
22, 6, 
23, 7, 
24)) 
& ":" & MID(TEXT( Date_Occurred__c ) , 15, 2) 
& IF(AND(VALUE(MID(TEXT( Date_Occurred__c ) , 12, 2)) >= 4, VALUE(MID(TEXT( Date_Occurred__c ) , 12, 2)) <= 15), " AM", " PM") 

Anna Lu 6Anna Lu 6
Anyone had this issue with daylight saving? There's always one hour different for data entered during winter seasons. Please let me know waht I should do with it.