Print this page

How do I convert a Date/Time field for any timezone?

Knowledge Article Number 000181642
Description This formula will allow you to convert any Date/Time field to display its value in another time zone. It will convert the Date (if necessary), the Time, and will account for Daylight Savings (using US start/end dates for DST).
Resolution

IF ( DATEVALUE(QA_ETOC__c) >=
DATE ( YEAR (DATEVALUE(QA_ETOC__c)),3,1)
+
(
14-
CASE( MOD(DATE ( YEAR (DATEVALUE(QA_ETOC__c)),3,1) - DATE (1900,1,7),7) ,
0,7,MOD(DATE ( YEAR (DATEVALUE(QA_ETOC__c)),3,1) - DATE (1900,1,7),7))
)
&&
DATEVALUE(QA_ETOC__c) <
DATE ( YEAR (DATEVALUE(QA_ETOC__c)),11,1)
+
(
7-
CASE( MOD(DATE ( YEAR (DATEVALUE(QA_ETOC__c)),11,1) - DATE (1900,1,7),7) ,
0,7,MOD(DATE ( YEAR (DATEVALUE(QA_ETOC__c)),11,1) - DATE (1900,1,7),7))
),
LEFT ( TEXT (QA_ETOC__c- 7/24 ), 16),
LEFT ( TEXT (QA_ETOC__c- 8/24), 16)
)

 

Replace QA_ETOC__c with your Date/Time Field (Tip: copy/paste into Word pad and use Ctrl+H to use the replace functionality)

The above formula works for PST (-8:00 GMT /-7:00GMT during daylight savings).

Change the -7 for Daylight Savings consideration and change the -8 for non-daylight saving consideration.  If you have IST you can replace both values (-7 and -8) with (+5.5) and it will work accordingly.

See also:

Using Date and Date/Time Values in Formulas
Date/Time Calculations in Formula Fields, Workflow Field Updates and Validation Rules





promote demote