Here’s the second hack submitted by Dustin Kost from 1st Light Energy (see his first dealing with distance between locations). This one will help you deal with US Daylight Savings time. Nothing worse than time changes throwing things off.
“Calculate when Daylight Savings Time start and stop dates in the US(until the government changes the rules again!). Currently (2016) DST begins on the second Sunday of March and ends on the first Sunday of November.
DST_Start_Date__c:
DATE ( YEAR (TODAY()),3,1)<br />+<br />( 14<br />-<br />CASE( MOD(DATE ( YEAR (TODAY()),3,1) - DATE (1900,1,7),7) ,<br />0,7,MOD(DATE ( YEAR (TODAY()),3,1) - DATE (1900,1,7),7))<br />)
DST_End_Date__c:
DATE ( YEAR (TODAY()),11,1)<br />+<br />( 7<br />-<br />CASE( MOD(DATE ( YEAR (TODAY()),11,1) - DATE (1900,1,7),7) ,<br />0,7,MOD(DATE ( YEAR (TODAY()),11,1) - DATE (1900,1,7),7))<br />)
Using the above, plus your own Timezone Offset formula in the User object, you can bend Salesforce's internal UTC datetime to your will.
FYI: you can't use TimeZoneSidKey so I made our own based on the User's State field. We use this in a button on Opportunity to prefill most of the fields we need on a new Event record screen for scheduling our sales reps.
