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

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

2 Answers 2

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)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .