Apex Code Development You need to sign in to do that
Don't have an account?
JWOODY 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
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
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.
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
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
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.
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
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
In case anybody needs this, subtracting 7 returns pacific military time, subtracting 4 returns eastern military time, ect. Assumes North American org of course.
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
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")
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)
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.
@ 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")
This thread helped me out, so I thought I would throw on here what I needed:
My goal was to have a checkbox formula field to check if a case came in after hours, so I could exclude those from our response time numbers.
Hour Number (24hr Format) EST *Note: for some reason I had to adjust the +5 to +4 to get the correct Hour for EST
A cool use for this that I didn't consider is grouping by the hour created so you can not only see how many cases came in each day but what hour.
HourCreated
FLOOR ( MOD( CreatedDate - ($System.OriginDateTime + 4/24),1) *24)
IsAfterHours
*Note: Our hours are Mon-Friday 8am to 5pm
IF(WEEKDAY(DATEVALUE(CreatedDate))=1, true,
IF(WEEKDAY(DATEVALUE(CreatedDate))=7, true,
IF(CreatedHour__c>17,true,
IF(CreatedHour__c<8,true,false))))