3

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. – Dan Wooding Dec 20 '16 at 15:19
3

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" 
)
  • @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) – Jack vardy Dec 21 '16 at 4:25
  • Yes .Sorry it's typo from my end .Yes use 5.5/24 everywhere – Mohith Shrivastava Dec 21 '16 at 4:30
  • @mohith-It worked fine! – Jack vardy Dec 21 '16 at 5:56

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.