[SalesForce] 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" 
)

Best Answer

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)

Now lets modify your logic to use this instead 

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" 
)
Related Topic