• bdowney
  • NEWBIE
  • 0 Points
  • Member since 2011

  • Chatter
    Feed
  • 0
    Best Answers
  • 0
    Likes Received
  • 0
    Likes Given
  • 0
    Questions
  • 3
    Replies
The following formula will calculate the number of working days (inclusive) between 2 dates. A working day is defined as Monday to Friday. Even if the start or end dates are a weekend, these are accommodated.

IF(AND((5 - (CASE(MOD( Start_Date__c - DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)) < (CASE(MOD(  End_Date__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0)) ),
((( End_Date__c  -   Start_Date__c ) + 1) < 7)),
((CASE(MOD(  End_Date__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0)) - (5 - (CASE(MOD(  Start_Date__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)))),
(((FLOOR((( End_Date__c  -  Start_Date__c ) - (CASE(MOD(  Start_Date__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 6, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0))) / 7)) * 5) +
(CASE(MOD(  Start_Date__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)) +
(CASE(MOD(  End_Date__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0))))


The Start Date and End Date fields are custom in the above example and can be replaced as required. If use of a DateTime field is required then the DATEVALUE function will be required.

I also recommend a simple field validation rule is added to check that the End Date is after the Start Date.
  • January 05, 2009
  • Like
  • 4
Hi does anyone know what formula I can use in the formula editor to calculate the number of working days between 2 date fields?
  • October 18, 2007
  • Like
  • 0
    Hi All! I have two formulas. The first one I created to keep a running ticker of the number of days, hours, & minutes a case has been open. It looks like this:

Text(FLOOR(NOW() - CreatedDate)) &" "&"day(s)"&" "&Text(FLOOR(MOD( (NOW() - CreatedDate) * 24 ,24))) &" "&"hrs"&" "&Text( FLOOR(MOD(((NOW() - CreatedDate) * 24)*60 ,60)))&" "&"min")
The second formula I got off the boards and it keeps track in business hrs the number of hrs a case has been open:

IF(AND(MOD(TODAY()-DATE(1900,1,7),7)< (NOW() - CreatedDate ), ((NOW() - CreatedDate )<7)),((NOW() - CreatedDate)-2) * 24, ((NOW() - CreatedDate)-(2 * ((NOW() - CreatedDate)/7))) * 24)

Both of these formulas work great, however, I would like to have the business hrs formula in the same format as the first formula. So it would be business hrs in days, hrs, and minutes. Does anyone have any ideas?? I keep playing with it but it is always off.