0

My formula field calculation of the difference of calculation date(date/time field) and current date(date/time field).

But it is differentiated in the manual calculation answer.

ABS( Calculating_Date__c - NOW() )*24) 

Above the formula is there to be used to calculate the time difference of Calculating_Date__c and current time.

But in case if the Calculating_Date__c value is 12/10/2016 6.20 AM and the current time is 12/10/2016 7.15 PM then the difference is coming by the formula answer is 0.20 but the manually the difference is 12.55(in hours).

why this difference.and how to get the correct value.

For answers thanks in advance.

1

Time values in Salesforce are stored in GMT while NOW() is returning time in the user's timezone. Get your org Default Time Zone difference and adjust your result, e.g. for my org it's CST (Chicago) GMT -5, so formula would look like ABS( Calculating_Date__c - NOW() )*24) - 5

  • My time zone GMT + 5.30. But also I get wrong answer in my manual calculating answer to compared to the formula answer to make the changes in my formula as like in your formula. – MohanRaj Oct 13 '16 at 6:20
  • do u need a difference in hours, not in a day fractions? – o-lexi Oct 13 '16 at 15:51
  • @MohanRaj I just edited the formula - it was incorrect, in your case (GMT +5:30) it should be ABS( Calculating_Date__c - NOW() )*24) + 5.5 – o-lexi Oct 13 '16 at 16:11
  • sorry sir, This answer is also providing the wrong answer. For example My Calculating Date is 10.05 PM and NOW() value is 10.35 AM here the difference is in only 12.30 hours but the answer of the formula returns is 23.49 in hours.I am also to fix the problem in this time difference bug if you find it please send me.Thank you Mohan. – MohanRaj Oct 14 '16 at 5:10
  • I forget to enteered the date value My calculating date value in the test time is 10/14/2016 for the both Calculating Date and NOW() then the time value is difference by Calculating Date and NOW() is 10.05PM and 10.35AM respectively and the formula output is 23.49 hours but the actual difference only 12.30 hours only. – MohanRaj Oct 14 '16 at 5:19
0

To all Below the answer of the question it's perfectly working for in my org.

((DATEVALUE( NOW()) - DATEVALUE(Calculating_Date__c))-1)*24 
+
 (24 - VALUE(MID(RIGHT((TEXT( Calculating_Date__c  + 0.2291)),9),0,2)))
+
 VALUE(MID(RIGHT((TEXT(  NOW() + 0.2291)),9),0,2))

Here I used the first line to be calculated to the difference between the two dates and the second line on the formula to taking the hours to be used in the Calculating Date.

Here it's should be used only for our time line in (my time line is GMT + 5.30) so I added to the 0.2291 value in the Calculating Date and NOW() field .

5 hours = 5*60(minutes) = 300 + 30 (for the GMT +5.30 Minutes)= 330
So 1440(for 24 hours in minute form) = 330/1440 = 0.2291

Thank You,Mohan

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.