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!