[SalesForce] Time Zone (and Daylight Savings) calculations (to a Text field)

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!

Best Answer

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

Related Solutions

[SalesForce] Datetime won’t save using correct time zone

The DateTime.valueOf(string) method has the following Usage note in the docs:

The specified string should use the standard date format “yyyy-MM-dd HH:mm:ss” in the local time zone.

It is ignoring the +1 UTC offset.

If I run the following as anonymous Apex:

String dtStr = '2014-06-19 14:44:00+01:00';
System.debug('Date/Time string to save:' + dtStr);
Datetime dt = Datetime.valueOf(dtStr);
System.debug('Date/Time to save:' + String.valueOf(dt));
System.debug('hour(): ' + dt.hour());
System.debug('hourGmt(): ' + dt.hourGmt());

I get:

12:33:15.258 (258899135)|USER_DEBUG|[4]|DEBUG|Date/Time string to save:2014-06-19 14:44:00+01:00
12:33:15.259 (259067845)|USER_DEBUG|[6]|DEBUG|Date/Time to save:2014-06-19 14:44:00
12:33:15.259 (259158685)|USER_DEBUG|[8]|DEBUG|hour(): 14
12:33:15.259 (259232093)|USER_DEBUG|[9]|DEBUG|hourGmt(): 21

In this particular instance my Salesforce users timezone is set to (GMT-07:00) Pacific Daylight Time (America/Tijuana).

As an alternative, try formatting the input string in the IS0 8601 format. You can then convert it to a UTC DateTime with:

String dtStr = '2014-06-19T14:44:00.000Z';
System.debug('Date/Time string to save:' + dtStr);
Datetime dt = (DateTime)json.deserialize('"' + dtStr + '"', datetime.class);
System.debug('Date/Time to save:' + String.valueOf(dt));
System.debug('hour(): ' + dt.hour());
System.debug('hourGmt(): ' + dt.hourGmt());

Which gives:

12:43:52.272 (272399372)|USER_DEBUG|[4]|DEBUG|Date/Time string to save:2014-06-19T14:44:00.000Z
12:43:52.273 (273942997)|USER_DEBUG|[7]|DEBUG|Date/Time to save:2014-06-19 07:44:00
12:43:52.274 (274047548)|USER_DEBUG|[9]|DEBUG|hour(): 7
12:43:52.274 (274124974)|USER_DEBUG|[10]|DEBUG|hourGmt(): 7

You can encode the timezone offset in the input date time using:

String dtStr = '2014-06-19T14:44:00.000+01:00';

If you can, transfer the value in UTC. It will make your task easier. I.e. what happens if/when BST changes due to daylight savings time.

[SalesForce] How to determine UTC for a date/time represented in a time zone other than the current user’s time zone

The Timezone class does exactly what you are looking for. As far as I know, it covers the entire tz database, and I know that it covers some historical changes in the database, as shown below.

Timezone tz = Timezone.getTimeZone('America/New_York');

//before the 2007 shift of DST into November
DateTime dtpre = DateTime.newInstanceGMT(2000, 11, 1, 0, 0, 0);
system.debug(tz.getOffset(dtpre));   //-18000000 (= -5 hours = EST)

//after the 2007 shift of DST into November
DateTime dtpost = DateTime.newInstanceGMT(2012, 11, 1, 0, 0, 0);
system.debug(tz.getOffset(dtpost));   //-14400000 (= -4 hours = EDT)

It also gets the exact time at which DST starts or ends.

Timezone tz = Timezone.getTimeZone('America/New_York');

DateTime dtpre = DateTime.newInstanceGMT(2014, 11, 2, 5, 59, 59);  //1:59:59AM local
system.debug(tz.getOffset(dtpre));   //-14400000 (= -4 hours = still on DST)

DateTime dtpost = DateTime.newInstanceGMT(2014, 11, 2, 6, 0, 0); //2:00:00AM local
system.debug(tz.getOffset(dtpost));  //-18000000 (= -5 hours = back one hour)

Best Answer

Related Solutions

[SalesForce] Datetime won’t save using correct time zone

[SalesForce] How to determine UTC for a date/time represented in a time zone other than the current user’s time zone

Related Topic