0

I have a formula for to calculating date/time fields difference in hours as followingly,

(((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 It's used to calculate the date/time difference in hours form. And here the value of the 0.2291 is the value of getting local time from the GMT time.(my org using time is GMT +5.30)

But I want now is To calculate the date/time difference in hours only for week days.Like If I select the day in the Calculating Date and Now() is 10/8/2016 and 10/15/2016 means it's provide the difference is in like the only in this dates the week days are 5 so the answer is provided to the 120 hours.

Here I also want this answer is also provided to the future days also in same way Like the Calculation Date and NOW() is 10/29/2016 and 10/15/2016 means it's provided the value in the answer is -120 hours(I want the difference in future days are in having negative values).

This 120 hours values are to be getted to the adding the value of remainding hours the calculation Date in 24 hours and the adding the NOW() values in the current hour compulsury.

And also the formula is also providing the appropriate correct result in the middle days also Like the condition of the Calculation Date and NOW() values are in same day and the difference of both two date's are in only 4 or less then 5 days.

Then that formula is calculated the weekdays value even I select the My CalCulating date and NOW() in this two date/time fields are in any one or both also pointing to the weekend days.

For answer's thanks in advance.Thank you, Mohan

1

If you wanted to calculate days you could do a formula. Quick google search yields:

Calculate the number of days between two dates while excluding weekends or excluding weekdays

https://help.salesforce.com/HTViewSolution?id=000004526

If you are wanting the number of hours you are left with:

Formula

ROUND( 8 * (
   ( 5 * FLOOR( ( DATEVALUE( ***date/time_1*** ) - DATE( 1900, 1, 8) ) / 7) +
    MIN(5, 
     MOD( DATEVALUE( date/time_1 ) - DATE( 1900, 1, 8), 7) +
     MIN( 1, 24 / 8 * ( MOD( date/time_1 - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1 ) ) )
    ) 
   )
 -
   ( 5 * FLOOR( ( DATEVALUE( date/time_2 ) - DATE( 1900, 1, 8) ) / 7) +
     MIN( 5,
      MOD( DATEVALUE( date/time_2 ) - DATE( 1996, 1, 1), 7 ) +
      MIN( 1, 24 / 8 * ( MOD( date/time_2 - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1) ) )
    )
   ) 
  ), 
0 )

https://developer.salesforce.com/docs/atlas.en-us.usefulFormulaFields.meta/usefulFormulaFields/formula_examples_dates.htm

Code

Business hours class

Use the BusinessHours methods to set the business hours at which your customer support team operates.

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_classes_businesshours.htm

  • Thank you for the reply. I already see that the above link help.salesforce.com/HTViewSolution?id=000004526 it's only working for earlier days only. and the I have the doubt in your giving code it's working perfect when I click week end days like 10/15/2016 and 10/08/2016 there.But it's not provide the correct result when I select the date like Calculating Date 9/30/2016 and the 10/15/2016 like it provide the result of 244 hours but the the 2 week different it provide the value of 240 hours(this value are replace taken by replacing your 8 hour instant of 24 hours) onlyin.it's why? – MohanRaj Oct 15 '16 at 11:39
  • @MohanRaj code is a copy and paste from sf help doc so no idea if it works as expected but I would assume it does. I don't use formulas for these situations as code with the bh class is eaiser, cleaner, more accurate, and imho just a better choice. – Eric Oct 15 '16 at 11:42

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.