5

In the below formula code, we have to alter the -4 or -5 values each time we enter or leave Daylight Savings time due to the fact that SF, when pulling the Text version of the Time, does it in GMT (or is it Zulu?). Any clues how I could make this handle it automatically?? Ideally, we'd alter the reference to TEXT( Event_Date_Time__c ) to somehow automatically cover the time conversion.

The reason for the not-so-friendly code is that we stick it into a Text field, and make it 24 hour time, so that it will sort properly in reports, so we have to put a leading zero on the <10 hours.

/* IN FORMULA.... -4 for EDST. (or -5 EST).. .. etc... */ 

if( 
and( 
VALUE( MID( TEXT( Event_Date_Time__c ), 12, 2 ) ) -4 < 10 , 
VALUE( MID( TEXT( Event_Date_Time__c ), 12, 2 ) ) -4 > -1 
) , "0" , null ) 

& 

TEXT( 
VALUE( MID( TEXT( Event_Date_Time__c ), 12, 2 ) ) -4 + 
if ( 
( VALUE( MID( TEXT( Event_Date_Time__c ), 12, 2 ) ) -4 ) < 1 
, 24 , 0 ) 
) 

& ":" & MID( TEXT( Event_Date_Time__c ), 15, 2 ) 

--- Added 2014-04-02 ---

My feeling on this is that until SF fixes or adds functionality to this so that TEXT() calls to the DateTime field do NOT revert to Zulu (OR, they TRANSLATE to the local time zone), is that I can get a developer to write a Scheduled Apex Call, which would go out every day at 1am "somewhere" and find out what our "adjustment" value is (for Eastern Time it's either -5 or -4 as seen in my code sample).

Then, as long as that value is in a "variable" that can be read GLOBALLY within the Org (not just one object, in this case Task/Activity), then we're resolved. The "-4" references in my code become (lacking a real example) "%DSTadjustment%".

PROBLEM WITH THAT: Records outside the "current DST mode" would be an hour off. MORE ideally would be something that evaluates the Date being used, and based on THAT DATE return the proper -4 or -5. (sigh)

--- Added 2015-05-06 ---

A new co-worker of mine, Asha, was smart enough to go out and find an Excel formula that does exactly what's needed, then translated it to SF Formula-speak. I will be posting it soon, as I need to get it incorporated in multiple ways (both formula and field updates), and I want to see if I can break it at all. All indications are that it works wonderfully and is completely dynamic to whatever date/time it's fed. Woohoo!

  • I dont think out of box we can achieve this .Through apex we can achieve this .Salesforce Lately came with TimeZone methods these can be used in code . – Mohith Shrivastava Mar 15 '13 at 18:44
  • Salesforce always stores datetime values as GMT (also called Zulu) natively in the db. These are corrected for display to the user based on the time zone on standard pages. – techtrekker Mar 16 '13 at 8:00
  • Unfortunately when translated through the TEXT() call in a formula, it becomes Zulu. :-( – AMM Mar 18 '13 at 17:59
  • You can sort by date fields in Salesforce reports by the user's correct timezone. If you're using external reporting, why not adjust that logic to sort based on GMT time? – James Loghry Apr 2 '14 at 21:37
  • The problem is the TEXT function. It returns Date Time variables in Zulu time. – AMM Apr 2 '14 at 21:38
0

Unfortunately, this will have to remain a manual effort as there is no way to get the time zone offset inside of a formula currently. I would advise trying to simply use the text field again and trying to get that to work rather than the formula field. It will make it easier for your users as well as properly display the time in their own time zone when accessing the system.

If you absolutely must continue to use this formula field, I would at least suggest using a custom setting instead of having -4 or -5 all over the place. I would create a custom setting for the current time zone offset. You can then access it like:

Formula Fields

Formula fields only work for hierarchy custom settings; they can’t be used for list custom settings.

{!$Setup.CustomSettingName__c.CustomFieldName__c}

Salesforce Docs: Accessing Custom Settings

  • I was hopeful there was a "good" solution by now. There is still a bug I've been aware of since almost day of of my involvement with SF (Sept 2010) where time calculations using certain formula elements do not work properly and use your LOCAL time AND the SERVER time (in our case, Eastern US versus Pacific US -3 hours) within the same formula, so calculations of things between 9pm and midnight (or was it midnight and 3am?) are off. Ugh. They have not paid enough attention to such things in my opinion. Thanks, in any case. (I currently keep -4/-5 as it's ONLY in this formula right now). – AMM Mar 26 '13 at 21:49
0

Not a completely automated method, but does solve data integrity issue for a moment:

IF( 
ISBLANK(WF_Lastest_Clock_Out__c),"0", 
TEXT(VALUE( 
Left( 
right( trim( 

if(today() > DATEVALUE("2014-04-01"), 
TEXT(WF_Lastest_Clock_Out__c+1/24), 
TEXT(WF_Lastest_Clock_Out__c) 
)),9),2))) 
&mid(right( trim( 
if(today() > DATEVALUE("2014-04-01"), 
TEXT(WF_Lastest_Clock_Out__c+1/24), 
TEXT(WF_Lastest_Clock_Out__c) ) ),9),4,2) 
)

Basically this hardcodes the date when daylight savings change and appends an extra hour.

  • 1
    A new co-worker went to the trouble of doing what I should have done LONG AGO - found an EXCEL FORMULA that actually DOES THE DEED. I have not tested it a TON of years back and forth, but generally speaking, it seems to work. I will be posting it soon, as she even recreated it in "SF formula-speak". Woohoo. (Thanks Asha!) :-) – AMM May 6 '15 at 18:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.