0

We have a formula field that will calculate the number of days (excluding weekends) between two Date/Time fields.

Please find the formula below

ROUND(24*((5*FLOOR((DATEVALUE(First_Response__c)-DATE(1900,01,01))/7) + MIN(5,MOD(DATEVALUE(First_Response__c)-DATE(1900,01,01), 7) + MIN(1, (MOD(First_Response__c -DATETIMEVALUE('1900-01-01 00:00:00'), 1))) )) - (5*FLOOR((DATEVALUE(CreatedDate)-DATE(1900,01,01))/7) + MIN(5, MOD(DATEVALUE(CreatedDate)-DATE(1900,01,01), 7) + MIN(1, (MOD(CreatedDate-DATETIMEVALUE('1900-01-01 00:00:00'), 1))) )) ),2)

Please find the image that has the negative value

enter image description here

enter image description here

  • Do you need the difference between two dates? – Reshma Feb 15 '17 at 11:20
  • @Reshma difference between two datetime excluding the weekends – Samuel Robert Feb 15 '17 at 11:21
  • If you want to factor BusinessHours into your calculation, Apex is the correct approach. – Adrian Larson Feb 15 '17 at 17:35
1

If the value of your formula is consistently right, just negative, then maybe just add * -1 at the end of the formula. Or add a second field that gives the absolute value - i.e., ABS(Field__c)

1

All you need is to switch calculations to look CreatedDate - First_Response__c:

ROUND( 24 * 
 ((5*FLOOR((DATEVALUE(CreatedDate)-DATE(1900,01,01))/7)
   + MIN(5, MOD(DATEVALUE(CreatedDate)-DATE(1900,01,01), 7)
    + MIN(1, (MOD(CreatedDate-DATETIMEVALUE('1900-01-01 00:00:00'), 1)))
    )
   )
   -
   (5*FLOOR((DATEVALUE(First_Response__c)-DATE(1900,01,01))/7)
   + MIN(5,MOD(DATEVALUE(First_Response__c)-DATE(1900,01,01), 7)
    + MIN(1, (MOD(First_Response__c -DATETIMEVALUE('1900-01-01 00:00:00'), 1)))
    )
   )
 )
,2)

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.