Formula to calculate business hours between date/time fields - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Cat SheltonCat Shelton 

Formula to calculate business hours between date/time fields

Hi there, 

I have 2 date/time fields and I need to work out the number of business hours between them, excluding Sundays and Bank Holidays. 

However, it's not as simple as that as our hours are different on a saturday! 

Our business hours are 8am - 8pm Monday - friday, and 9am - 6pm on a Saturday - I have the formula from the Salesforce docs, but how can I adjust this to fit my needs? 

(I have adjusted the formula below based on our weekdays start time) 
 
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 / 12 * ( MOD( date/time_1 - DATETIMEVALUE( '1900-01-08
08: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 / 12 * ( MOD( date/time_2 - DATETIMEVALUE( '1900-01-08
08:00:00' ), 1) ) )
)
)
),
0 )

 
Best Answer chosen by Cat Shelton
Eric PraudEric Praud
OMG, I'm such an idiot!!!!
I think I've got it. I was trying to reinvent the wheel here when all I had to do was to reproduce the "Monday to Friday" formual and make it work for Saturday only...
Try this, I did a few quick tests and it looks good so far:
ROUND( 12 * (
   ( 5 * FLOOR( ( DATEVALUE(  Replied_Date_Time__c  ) - DATE( 1900, 1, 8) ) / 7) +
    MIN(5, 
     MOD( DATEVALUE( Replied_Date_Time__c   ) - DATE( 1900, 1, 8), 7) +
     MIN( 1, 24 / 12 * ( MOD( Replied_Date_Time__c  - DATETIMEVALUE( '1900-01-08 08:00:00' ), 1 ) ) )
    ) 
   )
 -
   ( 5 * FLOOR( ( DATEVALUE(  Received_Date_Time__c  ) - DATE( 1900, 1, 8) ) / 7) +
     MIN( 5,
      MOD( DATEVALUE( Received_Date_Time__c   ) - DATE( 1996, 1, 1), 7 ) +
      MIN( 1, 24 / 12 * ( MOD( Received_Date_Time__c   - DATETIMEVALUE( '1900-01-08 08:00:00' ), 1) ) )
    )
   ) 
  ), 
2 )
+
ROUND( 9 * (
   (  FLOOR( ( DATEVALUE(  Replied_Date_Time__c  ) - DATE( 1900, 1, 6) ) / 7) +
    MIN(1, 
     MOD( DATEVALUE( Replied_Date_Time__c   ) - DATE( 1900, 1, 6), 7) +
     MIN( 1, 24 / 9 * ( MOD( Replied_Date_Time__c  - DATETIMEVALUE( '1900-01-06 09:00:00' ), 1 ) ) )
    ) 
   )
 -
   (  FLOOR( ( DATEVALUE(  Received_Date_Time__c  ) - DATE( 1900, 1, 6) ) / 7) +
     MIN( 1,
      MOD( DATEVALUE( Received_Date_Time__c   ) - DATE( 1900, 1, 6), 7 ) +
      MIN( 1, 24 / 9 * ( MOD( Received_Date_Time__c   - DATETIMEVALUE( '1900-01-06 09:00:00' ), 1) ) )
    )
   ) 
  ), 
2)

 

All Answers

Kapil BatraKapil Batra
Hi Cat,
Please try below formula
((5 * ( FLOOR( (DATEVALUE(date/time_1) - DATE( 1900, 1, 8) ) / 7 ) ) +
MIN( 5, MOD( DATEVALUE(date/time_1)- DATE( 1900, 1, 8), 7 ) ) )
-
(5 * ( FLOOR( (DATEVALUE(date/time_2)- DATE( 1900, 1, 8) ) / 7 ) ) +
MIN( 5, MOD( DATEVALUE(date/time_2)- DATE( 1900, 1, 8), 7 ) ) ))*8
Hope it helps !
 
Cat SheltonCat Shelton
Hi Kapil, 

Thank you but unfortunately that formula doesn't work - User-added image
Kapil BatraKapil Batra
Please copy and paste below formula as you are passing less parameters in MOD function : 
((5 * ( FLOOR( (DATEVALUE(Received_Date_Time__c) - DATE( 1900, 1, 8) ) / 7 ) ) +
MIN( 5, MOD( DATEVALUE(Received_Date_Time__c)- DATE( 1900, 1, 8), 7 ) ) )
-
(5 * ( FLOOR( (DATEVALUE(Replied_Date_Time__c)- DATE( 1900, 1, 8) ) / 7 ) ) +
MIN( 5, MOD( DATEVALUE(Replied_Date_Time__c)- DATE( 1900, 1, 8), 7 ) ) ))*8

 
Cat SheltonCat Shelton
Hi Kapil, I tried that but it then said I had too many parameters 
Eric PraudEric Praud
Hi Cat,

First, your formula is wrong since you forgot to change the first 8 to a 12.
Here's a formula that should work. I'm simply finding the number of Saturdays and adding 9 hours each time and subtracting the number of hours left on the last Saturday if date/time_2 happens to be on a Saturday:
ROUND( 12 * (
( 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 / 12 * ( MOD( date/time_1 - DATETIMEVALUE( '1900-01-08
08: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 / 12 * ( MOD( date/time_2 - DATETIMEVALUE( '1900-01-08
08:00:00' ), 1) ) )
)
)
),
0 )
+

(FLOOR(( date/time_2-  date/time_1)/7)
+
IF(OR (WEEKDAY(DATEVALUE(date/time_1))=7, WEEKDAY(DATEVALUE(date/time_2))=7, WEEKDAY(DATEVALUE(date/time_1))>WEEKDAY(DATEVALUE(date/time_2))),1,0))*9
-
IF(WEEKDAY(DATEVALUE(date/time_2))=7,
IF(HOUR(TIMEVALUE(date/time_2))<9
0, MIN(9, HOUR(TIMEVALUE(date/time_2))-9)),0)

 
Kapil BatraKapil Batra
Hi Cat,
I have tried the same in my org. I am having Date1, Date2 datetime fields and a formula number here. Please check below screenshot.
User-added image
((5 * ( FLOOR( (DATEVALUE(Date2__c) - DATE( 1900, 1, 8) ) / 7 ) ) +
MIN( 5, MOD( DATEVALUE(Date2__c)- DATE( 1900, 1, 8), 7 ) ) )
-
(5 * ( FLOOR( (DATEVALUE(Date1__c)- DATE( 1900, 1, 8) ) / 7 ) ) +
MIN( 5, MOD( DATEVALUE(Date1__c)- DATE( 1900, 1, 8), 7 ) ) ))*8

 
Eric PraudEric Praud
@Kapil, your formula calculates the number of business days, not business hours.
@Ankush, this is the formula @Cat is already using
Cat SheltonCat Shelton
Thanks Eric - i'm getting a syntax error on that formula though :( - also, would this account for any bank holidays that we have?

User-added image
Eric PraudEric Praud
You need to change date/time_1 and date/time_2 to your date/time fields.
This formula will not account for bank holidays, only weekends
Kapil BatraKapil Batra
@Eric, got yout point, Thanks for letting me know !  Actually after calculating the days I was multiplying it by 8 to get the hours but I just realised that in the requirement the business hours are different based on days.
You are in my Demigods of formula list. Keep up the good work !
Cat SheltonCat Shelton
Thanks Eric, sorry for being daft, i started work at 7 so I'm very tired in my defence! 

I have added the fields in now, but I'm getting the same Syntax error
Eric PraudEric Praud
There's no being daft, believe me, I've done WAAAAAAYYYY worse :)
I've seen it happen because of copying/pasting. Try toi remove all the unnecessary spaces, or type the formula yourself and this error should no longer show
ROUND(12*(
(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/12*(MOD(date/time_1-DATETIMEVALUE('1900-01-08
08: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/12*(MOD(date/time_2-DATETIMEVALUE('1900-01-08
08:00:00'),1)))
)
)
),
0)
+

(FLOOR((date/time_2-date/time_1)/7)
+
IF(OR(WEEKDAY(DATEVALUE(date/time_1))=7,WEEKDAY(DATEVALUE(date/time_2))=7,WEEKDAY(DATEVALUE(date/time_1))>WEEKDAY(DATEVALUE(date/time_2))),1,0))*9
-
IF(WEEKDAY(DATEVALUE(date/time_2))=7,
IF(HOUR(TIMEVALUE(date/time_2))<9
0,MIN(9,HOUR(TIMEVALUE(date/time_2))-9)),0)

 
Eric PraudEric Praud
I may have found a simpler way to calculate your business hours (at least shorter). I've added comments so it's easier to understand. They can be removed if you want.
I was also thinking that this only works in GMT, daylight savings are not taken into account. I see you're in the UK so we MIGHT (not 100% sure) be able to do something since DLS follow a pattern, but if your company is international, there is nothing we can do:
/*Find the number of weekdays*/
5*FLOOR((date/time_2-date/time_1)/7)
/*12 hours workday*/
*12
/*Subtract the number of remaining hours in the last day if it’s not a weekend*/
-
IF(CASE(WEEKDAY(DATEVALUE(date/time_2)),1,1,7,1,0)=0,
/*Check whether the time is before 8*/
IF(HOUR(TIMEVALUE(date/time_2))<8,
/*If so, do not subtract anything, otherwise subtract the difference between 8 AM and the time value of date/time_2, or the full 12 hours if after 8 PM*/
0,MIN(12,HOUR(TIMEVALUE(date/time_2))-8)),0)
/*Add the numbers of Saturdays between the 2 dates*/
+
(FLOOR((date/time_2-date/time_1)/7)
+
IF(OR(WEEKDAY(DATEVALUE(date/time_1))=7,WEEKDAY(DATEVALUE(date/time_2))=7,WEEKDAY(DATEVALUE(date/time_1))>WEEKDAY(DATEVALUE(date/time_2))),1,0))
/*9 hours day*/
*9
/*Subtract the number of remaining hours in the last day if it falls on a Saturday*/
-
IF(WEEKDAY(DATEVALUE(date/time_2))=7,
/*Check whether the time is before 9*/
IF(HOUR(TIMEVALUE(date/time_2))<9
/*If so, do not subtract anything, otherwise subtract the difference between 9 AM and the time value of date/time_2, or the full 9 hours if after 8 PM*/
0,MIN(9,HOUR(TIMEVALUE(date/time_2))-9)),0)

 
Cat SheltonCat Shelton
Thank you Eric that's brilliant! We are only UK based so the hours is fine, I have a few formulas that I adjust for BST and GMT as needed so I can include this in it too. I will give this a go very soon I just have to hop onto something else! 
Eric PraudEric Praud
Ok, great. I just figured that I was missing a part, the right formula would be:
/*Find the number of weekdays*/
5*FLOOR((date/time_2-date/time_1)/7)
/*12 hours workday*/
*12
/*Subtract the number of remaining hours in the last day if it’s not a weekend*/
-
IF(CASE(WEEKDAY(DATEVALUE(date/time_2)),1,1,7,1,0)=0,
/*Check whether the time is before 8*/
IF(HOUR(TIMEVALUE(date/time_2))<8,
/*If so, do not subtract anything, otherwise subtract the difference between 8 AM and the time value of date/time_2, or the full 12 hours if after 8 PM*/
0,MIN(12,HOUR(TIMEVALUE(date/time_2))-8)),0)
/*Do the same with Date/Time_1*/
-
IF(CASE(WEEKDAY(DATEVALUE(date/time_1)),1,1,7,1,0)=0,
IF(HOUR(TIMEVALUE(date/time_1))<8,
0,MIN(12,HOUR(TIMEVALUE(date/time_1))-8)),0)

/*Add the numbers of Saturdays between the 2 dates*/
+
(FLOOR((date/time_2-date/time_1)/7)
+
IF(OR(WEEKDAY(DATEVALUE(date/time_1))=7,WEEKDAY(DATEVALUE(date/time_2))=7,WEEKDAY(DATEVALUE(date/time_1))>WEEKDAY(DATEVALUE(date/time_2))),1,0))
/*9 hours day*/
*9
/*Subtract the number of remaining hours in the last day if it falls on a Saturday*/
-
IF(WEEKDAY(DATEVALUE(date/time_2))=7,
/*Check whether the time is before 9*/
IF(HOUR(TIMEVALUE(date/time_2))<9
/*If so, do not subtract anything, otherwise subtract the difference between 9 AM and the time value of date/time_2, or the full 9 hours if after 8 PM*/
0,MIN(9,HOUR(TIMEVALUE(date/time_2))-9)),0)

 
Eric PraudEric Praud
I'll stop spamming you, my last 2 formulas don't work properly, go with the 2nd one I sent you (that was adding to your original formula, without spaces)
Cat SheltonCat Shelton
Thanks Eric, sorry to ask but I just need to clarify - 

I have received_date_time__c and replied_date_time_c - which one is date/time_1 and 2? 

I've been filling it in and getting confused!!
Eric PraudEric Praud
Hi Cat,

Since you go date/time_1-date/time_2, the highest date should be 1, so I would assume it would be replied_date_time_c and therefore date/time_2 would be received_date_time__c. I had (yet) another mistake, so here it is, with the field names replaced
 
ROUND(12*(
(5*FLOOR((DATEVALUE(replied_date_time_c)-DATE(1900,1,8))/
7)+
MIN(5,
MOD(DATEVALUE(replied_date_time_c)-DATE(1900,1,8),7)+
MIN(1,24/12*(MOD(replied_date_time_c-DATETIMEVALUE('1900-01-08
08:00:00'),1)))
)
)
-
(5*FLOOR((DATEVALUE(received_date_time__c)-DATE(1900,1,8))/
7)+
MIN(5,
MOD(DATEVALUE(received_date_time__c)-DATE(1996,1,1),7)+
MIN(1,24/12*(MOD(received_date_time__c-DATETIMEVALUE('1900-01-08
08:00:00'),1)))
)
)
),
0)
+
(FLOOR((replied_date_time_c-received_date_time__c)/7)
+
IF(OR(WEEKDAY(DATEVALUE(replied_date_time_c))=7,WEEKDAY(DATEVALUE(received_date_time__c))=7,WEEKDAY(DATEVALUE(received_date_time__c))>WEEKDAY(DATEVALUE(replied_date_time_c))),1,0))*9
-
IF(WEEKDAY(DATEVALUE(received_date_time__c))=7,
IF(HOUR(TIMEVALUE(received_date_time__c))<9
0,MIN(9,HOUR(TIMEVALUE(received_date_time__c))-9)),0)

 
Cat SheltonCat Shelton
Thanks Eric, I took your advice and typed it in manually, but I'm still getting a syntax error, i've tried adjusting the parameters but it's not working. Sorry to keep asking, I'm not hugely brilliant at formulas yet! 

User-added image
Eric PraudEric Praud
Don't apologise!

I forgot a bracket at the line before the last one (after the 9), try this:
ROUND(12*(
(5*FLOOR((DATEVALUE(replied_date_time_c)-DATE(1900,1,8))/
7)+
MIN(5,
MOD(DATEVALUE(replied_date_time_c)-DATE(1900,1,8),7)+
MIN(1,24/12*(MOD(replied_date_time_c-DATETIMEVALUE('1900-01-08
08:00:00'),1)))
)
)
-
(5*FLOOR((DATEVALUE(received_date_time__c)-DATE(1900,1,8))/
7)+
MIN(5,
MOD(DATEVALUE(received_date_time__c)-DATE(1996,1,1),7)+
MIN(1,24/12*(MOD(received_date_time__c-DATETIMEVALUE('1900-01-08
08:00:00'),1)))
)
)
),
0)
+
(FLOOR((replied_date_time_c-received_date_time__c)/7)
+
IF(OR(WEEKDAY(DATEVALUE(replied_date_time_c))=7,WEEKDAY(DATEVALUE(received_date_time__c))=7,WEEKDAY(DATEVALUE(received_date_time__c))>WEEKDAY(DATEVALUE(replied_date_time_c))),1,0))*9
-
IF(WEEKDAY(DATEVALUE(received_date_time__c))=7,
IF(HOUR(TIMEVALUE(received_date_time__c))<9,
0,MIN(9,HOUR(TIMEVALUE(received_date_time__c))-9)),0)

 
Cat SheltonCat Shelton
Still getting the issue I'm afraid!
Eric PraudEric Praud
Same error (extra ')')?
Eric PraudEric Praud
Can you copy/paste the formula you're using (easier than a screenshot)
Cat SheltonCat Shelton
Sure! 
 
ROUND(12*(
(5*FLOOR((DATEVALUE( Replied_Date_Time__c  )-DATE(1900,1,8))/7)+
MIN(5,
MOD(DATEVALUE( Replied_Date_Time__c )-DATETIMEVALUE('1900-01-08 08:00:00'),1)))
)
)
-
(5*FLOOR((DATEVALUE( Received_Date_Time__c )-DATE(1900,1,8))/7)+
MIN(5,
MOD(DATEVALUE( Received_Date_Time__c )-DATE(1996,1,1),7)+
MIN(1,24/12*(MOD( Received_Date_Time__c -DATETIMEVALUE('1900-01-08 08:00:00'),1)))
)
)
),
0)
+
(FLOOR(( Replied_Date_Time__c - Received_Date_Time__c )/7)
+
IF(OR(WEEKDAY(DATEVALUE( Replied_Date_Time__c ))=7,WEEKDAY( Received_Date_Time__c ))=7,WEEKDAY(DATEVALUE( Received_Date_Time__c ))>WEEKDAY(DATEVALUE( Replied_Date_Time__c ))),1,0))*9
-
IF(WEEKDAY(DATEVALUE( Received_Date_Time__c ))=7,
IF(HOUR(TIMEVALUE( Received_Date_Time__c ))<9
0,MIN(9,HOUR(TIMEVALUE( Received_Date_Time__c ))-9)),0)

I couldn't see where you added the extra ) so I put it after the 9
Eric PraudEric Praud
I meant a comma, sorry.
Your other issue is on line 4, there's quite a bit missing, it should be:
MIN(5,
MOD(DATEVALUE(replied_date_time_c)-DATE(1900,1,8),7)+
MIN(1,24/12*(MOD(replied_date_time_c-DATETIMEVALUE('1900-01-08
08:00:00'),1)))

The whole formula should be this:
ROUND(12*(
(5*FLOOR((DATEVALUE(replied_date_time_c)-DATE(1900,1,8))/
7)+
MIN(5,
MOD(DATEVALUE(replied_date_time_c)-DATE(1900,1,8),7)+
MIN(1,24/12*(MOD(replied_date_time_c-DATETIMEVALUE('1900-01-08
08:00:00'),1)))
)
)
-
(5*FLOOR((DATEVALUE(received_date_time__c)-DATE(1900,1,8))/
7)+
MIN(5,
MOD(DATEVALUE(received_date_time__c)-DATE(1996,1,1),7)+
MIN(1,24/12*(MOD(received_date_time__c-DATETIMEVALUE('1900-01-08
08:00:00'),1)))
)
)
),
0)
+
(FLOOR((replied_date_time_c-received_date_time__c)/7)
+
IF(OR(WEEKDAY(DATEVALUE(replied_date_time_c))=7,WEEKDAY(DATEVALUE(received_date_time__c))=7,WEEKDAY(DATEVALUE(received_date_time__c))>WEEKDAY(DATEVALUE(replied_date_time_c))),1,0))*9
-
IF(WEEKDAY(DATEVALUE(received_date_time__c))=7,
IF(HOUR(TIMEVALUE(received_date_time__c))<9,
0,MIN(9,HOUR(TIMEVALUE(received_date_time__c))-9)),0)


 
Cat SheltonCat Shelton
Thanks Eric, I typed it in exactly as above and it's still throwing a fit
 
ROUND(12*(
(5*FLOOR((DATEVALUE( Replied_Date_Time__c )-DATE(1900,1,8))/
7)+
MIN(5,
MOD(DATEVALUE( Replied_Date_Time__c )-DATE(1900,1,8),7)+
MIN(1,24/12*(MOD( Replied_Date_Time__c -DATETIMEVALUE('1900-01-08 08:00:00'),1)))
)
)
-
(5*FLOOR((DATEVALUE( Received_Date_Time__c )-DATE(1900,1,8))/
7)+
MIN(5,
MOD(DATEVALUE( Received_Date_Time__c )-DATE(1996,1,1),7+
MIN(1,24/12*(MOD( Received_Date_Time__c -DATETIMEVALUE('1900-01-08 08:00:00'),1)))
)
)
),
0)
+
(FLOOR(( Replied_Date_Time__c - Received_Date_Time__c )/7)
+
IF(OR(WEEKDAY(DATEVALUE( Replied_Date_Time__c ))=7,WEEKDAY(DATEVALUE( Received_Date_Time__c ))=7,WEEKDAY(DATEVALUE( Received_Date_Time__c ))>WEEKDAY(DATEVALUE( Replied_Date_Time__c ))),1,0))*9
-
IF(WEEKDAY(DATEVALUE( Received_Date_Time__c ))=7,
IF(HOUR(TIMEVALUE( Received_Date_Time__c ))<9,
0,MIN(9,HOUR(TIMEVALUE( Received_Date_Time__c ))-9)),0)

it doesn't like the ), in the middle before the 0)
Eric PraudEric Praud
It looks like there was an extra bracket again.
I tested it in my own org wit hno syntax error, so copying/pasting should work:
ROUND(12*
(5*FLOOR((DATEVALUE(Replied_Date_Time__c )-DATE(1900,1,8))/7)+
MIN(5,MOD(DATEVALUE(Replied_Date_Time__c )-DATE(1900,1,8),7)+
MIN(1,24/12*(MOD(Replied_Date_Time__c -DATETIMEVALUE('1900-01-08 08:00:00'),1)))))-
(5*FLOOR((DATEVALUE(Received_Date_Time__c )-DATE(1900,1,8))/7)+MIN(5,MOD(DATEVALUE(Received_Date_Time__c )-DATE(1996,1,1),7)+MIN(1,24/12*(MOD(Received_Date_Time__c -DATETIMEVALUE('1900-01-08 08:00:00'),1))))),0)
+
(FLOOR((Replied_Date_Time__c -Received_Date_Time__c )/7)
+
IF(OR(WEEKDAY(DATEVALUE(Replied_Date_Time__c ))=7,WEEKDAY(DATEVALUE(Received_Date_Time__c ))=7,WEEKDAY(DATEVALUE(Received_Date_Time__c ))>WEEKDAY(DATEVALUE(Replied_Date_Time__c ))),1,0))*9
-
IF(WEEKDAY(DATEVALUE(Received_Date_Time__c ))=7,
IF(HOUR(TIMEVALUE(Received_Date_Time__c ))<9,
0,MIN(9,HOUR(TIMEVALUE(Received_Date_Time__c ))-9)),0)

 
Cat SheltonCat Shelton
That worked! I'll test it out and let you know shortly!! 
Cat SheltonCat Shelton
Well the syntax worked, not sure about the results in the field now though! :) User-added image
Eric PraudEric Praud
I think it's just a matter of brackets. Try this:
ROUND(12*
(5*FLOOR((DATEVALUE(Replied_Date_Time__c )-DATE(1900,1,8))/7)+
MIN(5,MOD(DATEVALUE(Replied_Date_Time__c )-DATE(1900,1,8),7)+
MIN(1,24/12*(MOD(Replied_Date_Time__c -DATETIMEVALUE('1900-01-08 08:00:00'),1)))))-
(5*FLOOR((DATEVALUE(Received_Date_Time__c )-DATE(1900,1,8))/7)+MIN(5,MOD(DATEVALUE(Received_Date_Time__c )-DATE(1996,1,1),7)+MIN(1,24/12*(MOD(Received_Date_Time__c -DATETIMEVALUE('1900-01-08 08:00:00'),1))))),0)
+
((FLOOR((Replied_Date_Time__c -Received_Date_Time__c )/7)
+
IF(OR(WEEKDAY(DATEVALUE(Replied_Date_Time__c ))=7,WEEKDAY(DATEVALUE(Received_Date_Time__c ))=7,WEEKDAY(DATEVALUE(Received_Date_Time__c ))>WEEKDAY(DATEVALUE(Replied_Date_Time__c ))),1,0))*9
-
IF(WEEKDAY(DATEVALUE(Received_Date_Time__c ))=7,
IF(HOUR(TIMEVALUE(Received_Date_Time__c ))<9,
0,MIN(9,HOUR(TIMEVALUE(Received_Date_Time__c ))-9)),0))

 
Cat SheltonCat Shelton
Same issue i'm afraid :( 
Eric PraudEric Praud
I can see some commas missing from the original formula, so I went back to the very first one from this artcile and tweaked it for you:
https://help.salesforce.com/articleView?id=formula_examples_dates.htm&type=5

Copy/paste should work
 
ROUND( 12 * (
   ( 5 * FLOOR( ( DATEVALUE(  Replied_Date_Time__c   ) - DATE( 1900, 1, 8) ) / 7) +
    MIN(5, 
     MOD( DATEVALUE( Replied_Date_Time__c   ) - DATE( 1900, 1, 8), 7) +
     MIN( 1, 24 / 12 * ( MOD( Replied_Date_Time__c    - DATETIMEVALUE( '1900-01-08 08:00:00' ), 1 ) ) )
    ) 
   )
 -
   ( 5 * FLOOR( ( DATEVALUE(  Received_Date_Time__c   ) - DATE( 1900, 1, 8) ) / 7) +
     MIN( 5,
      MOD( DATEVALUE( Received_Date_Time__c    ) - DATE( 1996, 1, 1), 7 ) +
      MIN( 1, 24 / 12 * ( MOD( Received_Date_Time__c    - DATETIMEVALUE( '1900-01-08 08:00:00' ), 1) ) )
    )
   ) 
  ), 
0 )
+
((FLOOR((Replied_Date_Time__c   -Received_Date_Time__c   )/7)
+
IF(OR(WEEKDAY(DATEVALUE(Replied_Date_Time__c   ))=7,WEEKDAY(DATEVALUE(Received_Date_Time__c   ))=7,WEEKDAY(DATEVALUE(Received_Date_Time__c   ))>WEEKDAY(DATEVALUE(Replied_Date_Time__c   ))),1,0))*9
-
IF(WEEKDAY(DATEVALUE(Received_Date_Time__c   ))=7,
IF(HOUR(TIMEVALUE(Received_Date_Time__c   ))<9,
0,MIN(9,HOUR(TIMEVALUE(Received_Date_Time__c   ))-9)),0))

 
Cat SheltonCat Shelton
Amazing! it's working! Thank you so so much for spending so much time on this for me, i really appreciate it!
 
Eric PraudEric Praud
Glad it's finally sorted! :)
Eric PraudEric Praud
This is going to be difficult to be honest... Not even sure where to start here
Cat SheltonCat Shelton
Hi Eric, actually managed to solve by swapping the middle 0 for a 2 and it works fine now :) 
Eric PraudEric Praud
Well done!
Apologies, I'm onto something else right now, so hadn't had time to look into it.
Th eonly issue here is that the saturday bit only takes full hours if Received_Date_Time__c is a Saturday
Cat SheltonCat Shelton
Ahh ok I possibly need to amend it somewhere else too then! 
Eric PraudEric Praud
It should work if you modify the last line:
0,MIN(9,HOUR(TIMEVALUE(Received_Date_Time__c   ))-9)),0))

to this:
0,MIN(9,(HOUR(TIMEVALUE(Received_Date_Time__c   ))-9)+(MINUTE(TIMEVALUE(Received_Date_Time__c   ))/60))),0))

 
Cat SheltonCat Shelton
Thanks Eric, tried it and saturdays broke!User-added image
Cat SheltonCat Shelton
Although actually, saturdays are being weird anyway - even with the original formula it's saying '9.00' for the same times as above 
Eric PraudEric Praud
Let me double check. A bit busy today, so bear with me please
Cat SheltonCat Shelton
Not a problem, absolutely no hurry, do feel free to contact me directly if it's easier when you get time :) 
Eric PraudEric Praud
OMG, I'm such an idiot!!!!
I think I've got it. I was trying to reinvent the wheel here when all I had to do was to reproduce the "Monday to Friday" formual and make it work for Saturday only...
Try this, I did a few quick tests and it looks good so far:
ROUND( 12 * (
   ( 5 * FLOOR( ( DATEVALUE(  Replied_Date_Time__c  ) - DATE( 1900, 1, 8) ) / 7) +
    MIN(5, 
     MOD( DATEVALUE( Replied_Date_Time__c   ) - DATE( 1900, 1, 8), 7) +
     MIN( 1, 24 / 12 * ( MOD( Replied_Date_Time__c  - DATETIMEVALUE( '1900-01-08 08:00:00' ), 1 ) ) )
    ) 
   )
 -
   ( 5 * FLOOR( ( DATEVALUE(  Received_Date_Time__c  ) - DATE( 1900, 1, 8) ) / 7) +
     MIN( 5,
      MOD( DATEVALUE( Received_Date_Time__c   ) - DATE( 1996, 1, 1), 7 ) +
      MIN( 1, 24 / 12 * ( MOD( Received_Date_Time__c   - DATETIMEVALUE( '1900-01-08 08:00:00' ), 1) ) )
    )
   ) 
  ), 
2 )
+
ROUND( 9 * (
   (  FLOOR( ( DATEVALUE(  Replied_Date_Time__c  ) - DATE( 1900, 1, 6) ) / 7) +
    MIN(1, 
     MOD( DATEVALUE( Replied_Date_Time__c   ) - DATE( 1900, 1, 6), 7) +
     MIN( 1, 24 / 9 * ( MOD( Replied_Date_Time__c  - DATETIMEVALUE( '1900-01-06 09:00:00' ), 1 ) ) )
    ) 
   )
 -
   (  FLOOR( ( DATEVALUE(  Received_Date_Time__c  ) - DATE( 1900, 1, 6) ) / 7) +
     MIN( 1,
      MOD( DATEVALUE( Received_Date_Time__c   ) - DATE( 1900, 1, 6), 7 ) +
      MIN( 1, 24 / 9 * ( MOD( Received_Date_Time__c   - DATETIMEVALUE( '1900-01-06 09:00:00' ), 1) ) )
    )
   ) 
  ), 
2)

 
This was selected as the best answer
Cat SheltonCat Shelton
I did wonder if I could do that but i wasn't sure how! it's working beautifully now! Thank you so much for your help! 
Eric PraudEric Praud
So sorry about all the faffing around here, not sure what happened (brain freeze!?).
Can I ask you to change the best answer so it's th eproper one in case anyone has the same issue and so they don't have to sift through about 40 comments :)
Cat SheltonCat Shelton
Done! It's absolutely fine, thanks ever so much again, you have saved me a HUGE headache. 
Cat SheltonCat Shelton
One more thing, how does it get negative numbers? as this should technically be 3.5ish 
User-added image
Eric PraudEric Praud
I understand the negative number since the calculation goes "replied date" - "received date". What I don't understand is that it adds 5 hours... I tested it with the same times (but swapped the dates so I had a positive number) and I get the same result (albeit a positive one).
Not too sure here...
Cat SheltonCat Shelton
Yeah very odd! 
Cat SheltonCat Shelton
I'll stick a new question up and see if anyone knows! 
 
Eric PraudEric Praud
It only seems to happen when both dates are the same day
Eric PraudEric Praud
Hi Cat,

I think I've got it. Now, my formula gets to 4995 characters (only 5 from the limit), but it seems to work even when both dates are on the same day:
IF(DATEVALUE(  date_time_1__c)=DATEVALUE(  date_time_2__c),
IF(WEEKDAY(DATEVALUE(  date_time_1__c))=7,
IF(
OR(
AND(HOUR(TIMEVALUE(date_time_1__c))>18, HOUR(TIMEVALUE(date_time_2__c))>18),
AND(HOUR(TIMEVALUE(date_time_1__c))<9, HOUR(TIMEVALUE(date_time_2__c))<9)),0,

(DATETIMEVALUE("4000-12-31 08:00:00")
+
MIN(date_time_1__c-DATETIMEVALUE("4000-12-31 08:00:00"), DATETIMEVALUE( TEXT(DATEVALUE(date_time_1__c))&" 18:00:00")-DATETIMEVALUE("4000-12-31 08:00:00")))-

(DATETIMEVALUE("1900-01-01 08:00:00")
+
MAX(date_time_2__c-DATETIMEVALUE("1900-01-01 08:00:00"), DATETIMEVALUE( TEXT(DATEVALUE(date_time_2__c))&" 09:00:00")-DATETIMEVALUE("1900-01-01 08:00:00")))),

IF(
OR(
AND(HOUR(TIMEVALUE(date_time_1__c))>20, HOUR(TIMEVALUE(date_time_2__c))>20),
AND(HOUR(TIMEVALUE(date_time_1__c))<8, HOUR(TIMEVALUE(date_time_2__c))<8)),0,
(DATETIMEVALUE("4000-12-31 08:00:00")
+
MIN(date_time_1__c-DATETIMEVALUE("4000-12-31 08:00:00"), DATETIMEVALUE( TEXT(DATEVALUE(date_time_1__c))&" 20:00:00")-DATETIMEVALUE("4000-12-31 08:00:00")))-

(DATETIMEVALUE("1900-01-01 08:00:00")
+
MAX(date_time_2__c-DATETIMEVALUE("1900-01-01 08:00:00"), DATETIMEVALUE( TEXT(DATEVALUE(date_time_2__c))&" 08:00:00")-DATETIMEVALUE("1900-01-01 08:00:00"))))),


  

ROUND(12*(
   ( 5 * FLOOR( ( DATEVALUE(  date_time_1__c  ) - DATE( 1900, 1, 8) ) / 7) +
    MIN(5, 
     MOD( DATEVALUE( date_time_1__c   ) - DATE( 1900, 1, 8), 7) +
     MIN( 1, 24 / 12 * ( MOD( date_time_1__c  - DATETIMEVALUE( '1900-01-08 08:00:00' ), 1 ) ) )
    ) 
   )
 -
   ( 5 * FLOOR( ( DATEVALUE(  date_time_2__c  ) - DATE( 1900, 1, 8) ) / 7) +
     MIN( 5,
      MOD( DATEVALUE( date_time_2__c   ) - DATE( 1900, 1, 8), 7 ) +
      MIN( 1, 24 / 12 * ( MOD( date_time_2__c   - DATETIMEVALUE( '1900-01-08 08:00:00' ), 1) ) )
    )
   ) 
  ),
0)

+
ROUND( 9 * (
   (  FLOOR( ( DATEVALUE(  date_time_1__c  ) - DATE( 1900, 1, 6) ) / 7) +
    MIN(1, 
     MOD( DATEVALUE( date_time_1__c   ) - DATE( 1900, 1, 6), 7) +
     MIN( 1, 24 / 9 * ( MOD( date_time_1__c  - DATETIMEVALUE( '1900-01-06 09:00:00' ), 1 ) ) )
    ) 
   )
 -
   (  FLOOR( ( DATEVALUE(  date_time_2__c  ) - DATE( 1900, 1, 6) ) / 7) +
     MIN( 1,
      MOD( DATEVALUE( date_time_2__c   ) - DATE( 1900, 1, 6), 7 ) +
      MIN( 1, 24 / 9 * ( MOD( date_time_2__c   - DATETIMEVALUE( '1900-01-06 09:00:00' ), 1) ) )
    )
   ) 
  ), 
0))

 
Cat SheltonCat Shelton
Hi Eric, thank you! it's close, no longer does a negative value, but it's looking like this now - I have a call with our consultant later on today so I'll see if he has any ideas as well 

User-added image
Eric PraudEric Praud
Crap! It needs to be multiplied by 24, but then, I go over the limit. 
However, I think I have it, since you don't want to round to the nearest hour, this works for me:
IF(DATEVALUE(  date_time_1__c)=DATEVALUE(  date_time_2__c),
24*
IF(WEEKDAY(DATEVALUE(  date_time_1__c))=7,
IF(
OR(
AND(HOUR(TIMEVALUE(date_time_1__c))>18, HOUR(TIMEVALUE(date_time_2__c))>18),
AND(HOUR(TIMEVALUE(date_time_1__c))<9, HOUR(TIMEVALUE(date_time_2__c))<9)),0,

(DATETIMEVALUE("4000-12-31 08:00:00")
+
MIN(date_time_1__c-DATETIMEVALUE("4000-12-31 08:00:00"), DATETIMEVALUE( TEXT(DATEVALUE(date_time_1__c))&" 18:00:00")-DATETIMEVALUE("4000-12-31 08:00:00")))-

(DATETIMEVALUE("1900-01-01 08:00:00")
+
MAX(date_time_2__c-DATETIMEVALUE("1900-01-01 08:00:00"), DATETIMEVALUE( TEXT(DATEVALUE(date_time_2__c))&" 09:00:00")-DATETIMEVALUE("1900-01-01 08:00:00")))),

IF(
OR(
AND(HOUR(TIMEVALUE(date_time_1__c))>20, HOUR(TIMEVALUE(date_time_2__c))>20),
AND(HOUR(TIMEVALUE(date_time_1__c))<8, HOUR(TIMEVALUE(date_time_2__c))<8)),0,
(DATETIMEVALUE("4000-12-31 08:00:00")
+
MIN(date_time_1__c-DATETIMEVALUE("4000-12-31 08:00:00"), DATETIMEVALUE( TEXT(DATEVALUE(date_time_1__c))&" 20:00:00")-DATETIMEVALUE("4000-12-31 08:00:00")))-

(DATETIMEVALUE("1900-01-01 08:00:00")
+
MAX(date_time_2__c-DATETIMEVALUE("1900-01-01 08:00:00"), DATETIMEVALUE( TEXT(DATEVALUE(date_time_2__c))&" 08:00:00")-DATETIMEVALUE("1900-01-01 08:00:00"))))),


  

12*(
   ( 5 * FLOOR( ( DATEVALUE(  date_time_1__c  ) - DATE( 1900, 1, 8) ) / 7) +
    MIN(5, 
     MOD( DATEVALUE( date_time_1__c   ) - DATE( 1900, 1, 8), 7) +
     MIN( 1, 24 / 12 * ( MOD( date_time_1__c  - DATETIMEVALUE( '1900-01-08 08:00:00' ), 1 ) ) )
    ) 
   )
 -
   ( 5 * FLOOR( ( DATEVALUE(  date_time_2__c  ) - DATE( 1900, 1, 8) ) / 7) +
     MIN( 5,
      MOD( DATEVALUE( date_time_2__c   ) - DATE( 1900, 1, 8), 7 ) +
      MIN( 1, 24 / 12 * ( MOD( date_time_2__c   - DATETIMEVALUE( '1900-01-08 08:00:00' ), 1) ) )
    )
   ) 
  )

+
 9 * (
   (  FLOOR( ( DATEVALUE(  date_time_1__c  ) - DATE( 1900, 1, 6) ) / 7) +
    MIN(1, 
     MOD( DATEVALUE( date_time_1__c   ) - DATE( 1900, 1, 6), 7) +
     MIN( 1, 24 / 9 * ( MOD( date_time_1__c  - DATETIMEVALUE( '1900-01-06 09:00:00' ), 1 ) ) )
    ) 
   )
 -
   (  FLOOR( ( DATEVALUE(  date_time_2__c  ) - DATE( 1900, 1, 6) ) / 7) +
     MIN( 1,
      MOD( DATEVALUE( date_time_2__c   ) - DATE( 1900, 1, 6), 7 ) +
      MIN( 1, 24 / 9 * ( MOD( date_time_2__c   - DATETIMEVALUE( '1900-01-06 09:00:00' ), 1) ) )
    )
   ) 
  ))

 
Cat SheltonCat Shelton
Hi Eric, 

Thanks so much for your help, I'm on a call at the moment and we found https://www.professorflow.com/post/salesforce-business-hours 

Which inserts an Apex class - I then created a flow and it's working perfectly in Sandbox now! 
Marija PetronijevicMarija Petronijevic
Hi Cat,

Salesforce admin-in-training here! Thank you for sharing the link to this package. I installed it, but I am not sure what to do next. How do I create a flow that updates a field on a record? I am trying to get a difference in business hours between a 'Status changed at' field and NOW(). 

Thanks in advance!
Cat SheltonCat Shelton
Hi Marija, 

Here are the images from the flow we implemented, hopefully it helps! 

User-added image
User-added image
User-added image
Marija PetronijevicMarija Petronijevic
Hi Cat,

Thanks so much for the very quick response, this is super helpful!