Convert GMT time to IST using formula field

I have a field ActivityDateTime whose value is 2016-12-08T11:51:00.000Z at backend. On front end value of ActivityDateTime is 8/12/2016 5:21 PM. I have created a formula field "Rescheduled_Date_Time__c" as Text type which has below formula.I wanted it to be in IST. It should return me 5:21 PM but it is returning 11:51 PM .Anything wrong with my formula .

``````IF(
OR(
VALUE( MID( TEXT( ActivityDateTime - 5.5 ), 12, 2 ) ) = 0,
VALUE( MID( TEXT( ActivityDateTime - 5.5 ), 12, 2 ) ) = 12
),
"12",
TEXT( VALUE( MID( TEXT( ActivityDateTime - 5.5 ), 12, 2 ) )
-
IF(
VALUE( MID( TEXT( ActivityDateTime - 5.5 ), 12, 2 ) ) < 12,
0,
12
)
)
)
& ":" &
MID( TEXT( ActivityDateTime - 5.5 ), 15, 2 )
& ":" &
MID( TEXT( ActivityDateTime - 5.5 ), 18, 2 )
& " " &
IF(
VALUE( MID( TEXT( ActivityDateTime - 5.5 ), 12, 2 ) ) < 12,
"AM",
"PM"
)
``````
• FYI the reason they are doing 5.5/24 is because with formulas in Salesforce a day is represented as 1. Three days would be represented as 3. You want to get hours in a days so it would be less than one. Specifically it would be 0.229166. It's always better to have Salesforce do the math because if you rounded to say 0.23 that's a different time than 0.239, when the engine does it you wouldn't need to worry about rounding issues. Dec 20, 2016 at 15:19

Your formula needs a modification .

When we do TEXT(DateTime) field SFDC converts the DateTime to TZ format which is in GMT .

Now to force GMT to IST we add +5.30 hours to the Datetime which can be done by below formula

TEXT(DateTime) + (5.5/24)

``````IF(
OR(
VALUE( MID( TEXT( ActivityDateTime + (5.5/24)), 12, 2 ) ) = 0,
VALUE( MID( TEXT( ActivityDateTime + (5.5/24) ), 12, 2 ) ) = 12
),
"12",
TEXT( VALUE( MID( TEXT( ActivityDateTime + (5.5/24) ), 12, 2 ) )
-
IF(
VALUE( MID( TEXT( ActivityDateTime + (5.5/24)), 12, 2 ) ) < 12,
0,
12
)
)
)
& ":" &
MID( TEXT( ActivityDateTime + (5.5 /24)), 15, 2 )
& ":" &
MID( TEXT( ActivityDateTime + (5.5/24) ), 18, 2 )
& " " &
IF(
VALUE( MID( TEXT( ActivityDateTime + (5.5 /24)), 12, 2 ) ) < 12,
"AM",
"PM"
)
``````
• @mohith-Your formula looks same which i have used. Can u tell me what will be the value of "ActivityDateTime ". Will it be equal to TEXT(DateTime) + (5.5/24) Dec 21, 2016 at 4:25
• Yes .Sorry it's typo from my end .Yes use 5.5/24 everywhere Dec 21, 2016 at 4:30
• @mohith-It worked fine! Dec 21, 2016 at 5:56