0

Can anyone lend a hand? I have fields that are based on local time, e.g. 9 AM in Riga, Latvia, and I need to reflect the time in UTC for the reports. So when I pull the report, it should be 7AM.

If I use DATETIMEVALUE, it's pulling in the same value, not UTC.

Would this formula work, and if so, how do I adapt it for UTC from PST? Having a total blank for what I need to do.

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

Thanks in Advance.

3

Dates are always stored in Salesforce in GMT, and then converted to the viewing user's time zone when the field is referenced (e.g. viewed in a page or report, or from another formula).

If you wrap a Date/Time field in the TEXT() formula function, it returns the GMT value instead of converting it first. (See Converting Between Date/Time and Text in the Using Date and Date/Time in Formulas documentation.) So TEXT(Departure_Date_And_Time__c) should return your value in GMT -- not exactly UTC, but it might work for your purposes -- and then you can use text manipulation to reformat it.

|improve this answer|||||
  • Hey, that was the first thing I tried but it's still a few hours off. Some of the dates are totally random. The thing is that the dates are for "take off" and "arrival" in the country and so are stored in date/time, but then I want to see them UTC/GMT. So, I text wrapped it and it's still off, and I figured it was because of the user's time settings... but the user is also in UTC. Thoughts? – gato negro Feb 19 '16 at 18:59

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.