Formula to calculate business hours between two date/time fields, excluding holidays and weekends? - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Caramie McCollumCaramie McCollum 

Formula to calculate business hours between two date/time fields, excluding holidays and weekends?

Hello all!

I have read several previous questions and answers, and still can't seem to find the right combination of what I'm looking for.

I need a formula that will calculate the difference between two date/time fields, in business hours only, while still excluding weekends.

I have found how to calculate business hours without excluding weekends, and how to exclude weekends, but only in business days. Anyone know how to take both into account?
Jigar ShahJigar Shah
The following knoweldge article from Salesforce provides the exact formula to compute Business Days between 2 dates while excluding weekends.

https://help.salesforce.com/articleView?id=000004526&type=1

Please mark this thread as SOLVED and answer as the BEST ANSWER if it helps address your issue.
Sunil SarillaSunil Sarilla
Hi Caramie,
+Jigar but since you mentioned your fields are date/time fields, please use the below formula and here is the link to the same
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 )
The formula rounds the value it finds to the nearest hour and assumes an 8–hour, 9 a.m. – 5 p.m. PDT work day
 
Caramie McCollumCaramie McCollum
@Jigar - thanks, but that calculates the difference in business days. I'm looking for business hours.

@Sunil - thank you! I see this returns the value in hours, rounding to the nearest hour. Does this exclude weekend hours? And how to do change the formula to match a 9-hour, 8 a.m. - 5 p.m. EST work day?
Sunil SarillaSunil Sarilla
Hi Caramie,
Change the formula to the below
ROUND( 9 * (
   ( 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 12: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 12:00:00' ), 1) ) )
    )
   ) 
  ), 
0 )
I have highlighted the changes in Bold
changed 8 to 9 in the first line of formula
and changed the DATETIMEVALUE( '1900-01-08 16:00:00' ) to DATETIMEVALUE( '1900-01-08 12:00:00' )
as DateTime values are stored in GMT, you will have to add the difference of the hours.
8:00 am EST is 12:00 PM in GMT (I have assumed its 4 hr difference between)
Priyanka SPriyanka S
Hi,

Can anyone please explain in plain english how the above formula works.. I am not able to completely understand the formula, it is calculating no of business days first by taking a refernce date as monday, but why are we using this line: MIN( 1, 24 / 8 * ( MOD( date/time_2 - DATETIMEVALUE( '1900-01-08 12:00:00' ), 1) ) ) and when I separately use this line in my test formula fied, it is giving me some random number like 0.78 etc... may i know what is this about?
Jesse WolffJesse Wolff
If you haven't already see it, the answer is here: https://developer.salesforce.com/docs/atlas.en-us.usefulFormulaFields.meta/usefulFormulaFields/formula_examples_dates.htm

Scroll down or page find to "Finding the Number of Business Hours Between Two Date/Times".
Jake GarrisJake Garris
This seems to be using a generic definition of 'business hours' ... does anyone know how to calculate the Business Days and/or Business Hours between two dates by using the 'Business Hours' settings in the system (which sets time zone, hours of operation each day, and holidays)?

Example:
We have several service centers around the globe.
I want to be able to calculate (in business hours) how long it takes the assigned service center to move a Case through a certain status. I am already recording (in Date/Time fields) the time that the Case enters and exits the status. I can therefore calculate the difference between these 2 date/time fields ... but I want to use the respective 'Business Hours' setting to show how many business hours elapsed between the two timestamps.

Can anyone help?
Caramie McCollumCaramie McCollum
@Jake Garris

Since your org is using the standard Service Cloud object, Cases, could you use milestone? You can use a process or workflow to populate the Completed Date/Time of each milestone as the Case meets the status criteria you set. Milestones are tracked in business hours.
 
Jake GarrisJake Garris
@Caramie McCollum

Thanks for the suggestion, I will look into Milestones.
Do you know if I can specify which 'Business Hours' setting to use - based on certain criteria (like assigned Service Center or Owner)?
Caramie McCollumCaramie McCollum
@Jake Garris

Yes, you can specify the business hours on the milestone. Or, if the same milestone may be used on multiple accounts that are supported in different business hours, you can set the business hours on the Etitlement record that houses the milestones. You can set criteria for the milestone, by field value, record type, etc. to determine which milestones apply to which Cases.
Jake GarrisJake Garris
@Caramie McCollum

Thanks again - I will look into this and see if it works.
I will let you know.
Rupert MarinRupert Marin
The problem with the formula provided above is that it breaks if the start time is before the actual working hours set in the formula ie 16:00:00 GMT.  It also doesn't take into account daylight saving time, so the caldulated hours will be different from November to March. I have been looking for a fix/working around for this for the last two days.  Does anyone have a solution for this issue as yet?
Alec AshfordAlec Ashford
@Rupert Marin I'm dealing with the same challenges (particularly the formula breaking when the start date is before business hours. Did you ever figure out how to solve?
Justin LubbertJustin Lubbert
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 )

Is the 1996 piece a typo or can someone explain why it is in Salesforce's example? I'm trying to understand the logic of the formula and this is choking me up. 
Rick CulbrethRick Culbreth
@Sunil Sarilla - thank you! We had a new case with a 2 business hour SLA.  The formula was just what I needed. 
Rupal SethRupal Seth
Hi,

How can I modify the formula so that it considers the 24 hours business hours but the time should stop on weekends i.e. stop by Friday 6 pm PST and again start by Sunday 6:30 pm PST
Paul JohnsonPaul Johnson
@Rupert Marin & @Alec Ashford

Did you ever figure out the issue of the formula breaking if the start time is before the actual working day start?
Evelyn TomerEvelyn Tomer
I am wondering the same thing as @Rupal Seth --- how can we only exclude weekends?
Rupert MarinRupert Marin
I have not found a work around as yet.  It's just an error we've come to accept
Evelyn TomerEvelyn Tomer
We are using the below formula, which works great except when records are created on a weekend the result becomes negative. Trying to figure out how to solve the negative hours issue.

((Inspection_Complete__c - Opened__c) * 24) - ( 

Case( Opened_Day_of_Week__c , 
"Sunday", Case(ROUND(Inspection_Complete__c - Opened__c 
,0),0,0,1,1,2,1,3,1,4,1,5,1,6,1,7,2,8,2,9,2,10,2,11,2,12,3,13,4,4), 
"Monday", Case(ROUND(Inspection_Complete__c - Opened__c 
,0),0,0,1,0,2,0,3,0,4,0,5,0,6,1,7,2,8,2,9,2,10,2,11,2,12,3,13,4,4), 
"Tuesday", Case(ROUND(Inspection_Complete__c - Opened__c 
,0),0,0,1,0,2,0,3,0,4,0,5,1,6,2,7,2,8,2,9,2,10,2,11,2,12,3,13,4,4), 
"Wednesday", Case(ROUND(Inspection_Complete__c - Opened__c 
,0),0,0,1,0,2,0,3,0,4,1,5,2,6,2,7,2,8,2,9,2,10,2,11,3,12,4,13,4,4), 
"Thursday", Case(ROUND(Inspection_Complete__c - Opened__c 
,0),0,0,1,0,2,0,3,1,4,2,5,2,6,2,7,2,8,2,9,2,10,3,11,4,12,4,13,4,4), 
"Friday", Case(ROUND(Inspection_Complete__c - Opened__c 
,0),0,0,1,0,2,1,3,2,4,2,5,2,6,2,7,2,8,2,9,3,10,4,11,4,12,4,13,4,4), 
"Saturday", Case(ROUND(Inspection_Complete__c - Opened__c 
,0),0,0,1,1,2,2,3,2,4,2,5,2,6,2,7,2,8,3,9,4,10,4,11,4,12,4,13,4,4), 
0) * 24)
Paul HeidbrierPaul Heidbrier
We're using the same formula Sunil provided, but getting 0.00 in some of our results. Any suggestion on what would be causing this?
Jimmy PerezJimmy Perez
I tried the formula, but when testing the value jumped from one hour (created a case 1 hour before end-of-day) to 10 right after midnight.  It came back down to one at start-of-day.  Suggestions?
Dan BontragerDan Bontrager
Business hours formula not working for start time outside business hours and end time within business hours

I have hit a roadblock and I'm wondering if someone on here can help me out.  I am looking to have a formula that shows the business hours between the Actual End Date (BMCServiceDesk__Actual_End_Date__c) and Actual Start Date (BMCServiceDesk__Actual_Start_Date__c).  I found the below links to help with creating the formula:
https://stackoverflow.com/questions/11126378/salesforce-formula-calculate-business-hours-between-two-dates/11129346
 
 
I have the formula modified now like the below code.  We have a 9-hour work day (8 AM - 5 PM) and we are in the Central Time Zone, so we use ’13:00:00’ in the formula (13:00:00 GMT = 1 PM GMT = 8 AM CT).  We also want to round the ‘hours’ values to 2 decimal places.
 
ROUND(9*(
(5*FLOOR((DATEVALUE(BMCServiceDesk__Actual_Start_Date__c)-DATE(1996,01,01))/7) +
MIN(5,
MOD(DATEVALUE(BMCServiceDesk__Actual_Start_Date__c)-DATE(1996,01,01), 7) +
    MIN(1, 24/9*(MOD(BMCServiceDesk__Actual_Start_Date__c-DATETIMEVALUE('1996-01-01 13:00:00'), 1)))
))
-
(5*FLOOR((DATEVALUE(BMCServiceDesk__Actual_End_Date__c)-DATE(1996,01,01))/7) +
MIN(5,
MOD(DATEVALUE(BMCServiceDesk__Actual_End_Date__c)-DATE(1996,01,01), 7) +
    MIN(1, 24/9*(MOD(BMCServiceDesk__Actual_End_Date__c-DATETIMEVALUE('1996-01-01 13:00:00'), 1)))
))
),2)
 
In most of my test cases this is working as expected.  When both the start and end are outside the business hours or on the weekends, the value is returned as ‘0.00’.  When the start is within the business hours and, regardless if the end is within the business hours or after it, the value is correctly calculated.
 
The issue comes when the start is outside the business hours and the end is within business hours.  For example, a change with a start of ‘4/19/19 4:45 AM’ and an end of ‘4/19/19 9:00 AM’ should calculate to ‘1.00’ (8 AM to 9 AM); instead, it calculates as ‘-8.00’.  It appears that any time prior to 8:00 AM shows as ‘-8.00’ back to 1:00 AM – 12:59 AM corrects to ‘1.00’ and is correct even back into previous day business hours.
 
In another example, if a cross an entire day with a change with a start of ‘4/18/19 4:45 AM’ and an end of ‘4/19/19 9:00 AM’, it should calculate to ‘10.00’ (one entire 9-hour day and 8 AM – 9 AM on the second day); instead, it calculates as ‘1.00’.
 
Can anyone help modified the above formula to get correct business hour calculations for start times outside business hours and end times within business hours?
 
Renee NicholusRenee Nicholus
@dan bontrager I am having the same issue - when the start time is before business hours, and the stop time is during business hours, the result is negative.  I am also having an issue when the start time and stop time are both outside business hours (even if there are business hours inbetween) - the result for that scenario is 0.  The only scenario it seems to work for is if both the start time and stop time are within business hours.  I have opened a case with Salesforce because this is a formula on their developer site, so I would expect that it works properly.  Wondering if you or anyone else has tried this, and received a solution to this issue.  
Dan BontragerDan Bontrager

@renee nicholus I have not opened a salesforce case or recivied any suggestions per this forum, so I still don't have a solution.  I did receive one suggestion when I posted the same message on Stack Overflow - that post is here:

https://stackoverflow.com/questions/56293751/salesforce-formula-business-hours-formula-not-working-for-start-time-outside-bu

For me, while the suggested formula there fixes the test case I menitoned above, it fails other test cases, failing to skip weekend days. For example: With a start of '4/18/19 4:15 PM' and an end of 4/22/19 7 PM' should calculate to 18.75 hours (4:15 PM – 5 PM on 4/18 & 8 AM – 5 PM on 4/19 & 8 AM – 5 PM on 4/22 – skips the weekend days)...however, it calculates as 36.75.  Another example: With a start of '4/20/19 4:15 PM' and an end of 4/20/19 7 PM' should calculate to 0.00 hours (skips the weekend days)...however, it calculates as 0.75.

Please reply post on here if you get assistance from salesforcea and get it figured out.  Thanks.

Renee NicholusRenee Nicholus
I opened a case with Salesforce and they gave me the formula below. It resolves all the issues I was experiencing. Please test and let me know if it resolves your issues as well.

9 *((5*FLOOR((DATEVALUE(Stop_Time__c)-DATE(1900,1,8))/7) + MIN(5, MOD(DATEVALUE(Stop_Time__c)-DATE(1900,1,8),7)+ MIN(1,24/9*(MOD(Stop_Time__c-DATETIMEVALUE('1900-01-08 13:00:00'), 1))) )) - (5*FLOOR((DATEVALUE(Start_Time__c + MAX(DATETIMEVALUE(TEXT(DATEVALUE(Start_Time__c))&" 13:00:00")-Start_Time__c,0))- DATE(1900,1,8))/7)+ MIN(5, MOD(DATEVALUE(Start_Time__c+MAX(DATETIMEVALUE(TEXT(DATEVALUE(Start_Time__c))&" 13:00:00")-Start_Time__c,0))-DATE(1900,1,8),7)+ MIN(1,24/9*(MOD(Start_Time__c + MAX(DATETIMEVALUE(TEXT(DATEVALUE(Start_Time__c))&" 13:00:00")-Start_Time__c,0)-DATETIMEVALUE('1900-01-08 13:00:00'), 1))))) )
Megan KnightMegan Knight
@Renee thank you for sharing this formula!! I've been trying to figure this out for the past 2 weeks but I kept getting incorrect or negative hours. I've tested this formula on a couple cases so far and it looks to be accurate! I just need to test outside of business hours to see how it works, but so far so good!
Megan KnightMegan Knight
hmm I tested it overnight and instead of showing 0.35 business hours, it shows 8.35 business hours. Any idea where the extra 8 hours are coming from?
Paul HeidbrierPaul Heidbrier
Experienced the same issue, except it added an extra 10 hours. 
Renee NicholusRenee Nicholus
Megan and Paul, can you please give me examples of your start and stop times, as well as the business hours results you are receiving?  The formula I shared was tailered to 8am-5pm CST.  Here is an example of results for one record. 
Start Time: 10/1/2017 12:00 AM
Stop Time: 10/6/2017 1:52 PM
Business Hours (CST): 41.87
Megan KnightMegan Knight
Hi Renee,

I changed the formula to reflect an 8 hour day (9am-5pm CST). My example is:
Start Time: 7/8/2019 4:38pm
Stop Time: 7/9/2019 6:54am
Business Hours: 8.35

It should only be 0.35 since there were only 22 minutes left in the business dayon 7/8 and the response on 7/9 was before business hours started.

This is how I updated the formula you pasted - does it look like I made a mistake somewhere?

8 *((5*FLOOR((DATEVALUE(First_Response__c )-DATE(1900,1,8))/7) + MIN(5, MOD(DATEVALUE(First_Response__c )-DATE(1900,1,8),7)+ MIN(1,24/8*(MOD(First_Response__c -DATETIMEVALUE('1900-01-08 14:00:00'), 1))) )) - (5*FLOOR((DATEVALUE(CreatedDate + MAX(DATETIMEVALUE(TEXT(DATEVALUE(CreatedDate))&" 14:00:00")-CreatedDate,0))- DATE(1900,1,8))/7)+ MIN(5, MOD(DATEVALUE(CreatedDate+MAX(DATETIMEVALUE(TEXT(DATEVALUE(CreatedDate))&" 14:00:00")-CreatedDate,0))-DATE(1900,1,8),7)+ MIN(1,24/8*(MOD(CreatedDate + MAX(DATETIMEVALUE(TEXT(DATEVALUE(CreatedDate))&" 14:00:00")-CreatedDate,0)-DATETIMEVALUE('1900-01-08 14:00:00'), 1))))) )
Paul HeidbrierPaul Heidbrier
Hi Renee, 

I'm also in CST. 
Start Time: 7/8/2019 11:25 AM
End Time: 7/8/2019 5:12 PM
Business Hours Taken to Address Case14.42
Renee NicholusRenee Nicholus
Hey Paul, I am getting the correct number for your scenario (6.00 business hours).
Megan, I am getting the same number as you, which is unfortunately incorrect.  

I did some further testing, and while some of the scenarios are fixed by the new formula, there are still 3 that remain.  Also, 1 scenario that used to work is no longer working.  I went back to Salesforce with my findings and I'll let you know if I hear back with a better formula.

My complete results for the old and new formulas are below, in case anyone is interested:
User-added image
 
Claire SunderlandClaire Sunderland
Thanks for sharing Renee! This got me closer to my formula but the problem I'm running into is 

Monday-Thursday business hours are 9 hours
Friday is only 8 hours.

Do you know how I can account for this? 
Megan KnightMegan Knight
Thanks for sharing your findings, Renee! It appears all the failed scenarios for the updated formula involve stop times before business hours. Please keep us posted on what support says! I also have a case open with them right now about this and will post any updates I get.
Dan BontragerDan Bontrager
@renee nicholus Thank you for working with salesforce on this.  I’ve read through the other replies from yesterday and I’ll put my full testing below for others to see, but I am in the same situation as others…I cannot find the right formula to pass all my test cases.  I’ll keep trying what salesforce suggests back to you. Thanks again!

My scenario: I am using this to find the business hours between the Actual Start Date & Time and Actual End Date & Time on the Change Report object.

1st formula tried:
ROUND(9*((5*FLOOR((DATEVALUE(BMCServiceDesk__Actual_Start_Date__c)-DATE(1996,01,01))/7) +
MIN(5,MOD(DATEVALUE(BMCServiceDesk__Actual_Start_Date__c)-DATE(1996,01,01), 7) +
MIN(1, 24/9*(MOD(BMCServiceDesk__Actual_Start_Date__c-DATETIMEVALUE('1996-01-01 13:00:00'), 1)))))-
(5*FLOOR((DATEVALUE(BMCServiceDesk__Actual_End_Date__c)-DATE(1996,01,01))/7) +
MIN(5,MOD(DATEVALUE(BMCServiceDesk__Actual_End_Date__c)-DATE(1996,01,01), 7) +
MIN(1, 24/9*(MOD(BMCServiceDesk__Actual_End_Date__c-DATETIMEVALUE('1996-01-01 13:00:00'), 1)))))),2)

This fails to correctly calculate business hours when the start is before 8 AM CT and the end is after 8 AM CT (example: start = 4:45 AM & end = 9:00 AM, it shows “-8.00” instead of “1.00”

2nd formula tried:
FLOOR((BMCServiceDesk__Actual_End_Date__c - BMCServiceDesk__Actual_Start_Date__c)/7)*5*9 +
MIN(5,FLOOR(MOD((BMCServiceDesk__Actual_End_Date__c - BMCServiceDesk__Actual_Start_Date__c),7)))*9+
IF((TIMEVALUE(BMCServiceDesk__Actual_End_Date__c)-TIMEVALUE("13:00:00.00"))/(3600*1000)>16,0,
MIN(9,(TIMEVALUE(BMCServiceDesk__Actual_End_Date__c)-TIMEVALUE("13:00:00.00"))/(3600*1000))) -
IF((TIMEVALUE(BMCServiceDesk__Actual_Start_Date__c)TIMEVALUE("13:00:00.00"))/(3600*1000)>16,0,
MIN(9,(TIMEVALUE(BMCServiceDesk__Actual_Start_Date__c)-TIMEVALUE("13:00:00.00"))/(3600*1000)))

This fails to correctly calculate business hours when the time crosses into or is entirely in the weekend (example: start = 4:15 PM Thursday, end = 7:00 PM Monday, it shows “36.75” instead of “18.75”; start = 4:15 PM Saturday, end = 7:00 PM Saturday, it shows “0.75” instead of “0.00”

3rd formula tried:
9 *((5 * FLOOR((DATEVALUE(BMCServiceDesk__Actual_End_Date__c) - DATE(1900,1,8))/7) +
MIN(5, MOD(DATEVALUE(BMCServiceDesk__Actual_End_Date__c) - DATE(1900,1,8),7) +
MIN(1,24/9 * (MOD(BMCServiceDesk__Actual_End_Date__c - DATETIMEVALUE('1900-01-08 13:00:00'), 1))) )) - (5 * FLOOR((DATEVALUE(BMCServiceDesk__Actual_Start_Date__c +
MAX(DATETIMEVALUE(TEXT(DATEVALUE(BMCServiceDesk__Actual_Start_Date__c))&" 13:00:00") - BMCServiceDesk__Actual_Start_Date__c,0)) - DATE(1900,1,8))/7) +
MIN(5,MOD(DATEVALUE(BMCServiceDesk__Actual_Start_Date__c +
MAX(DATETIMEVALUE(TEXT(DATEVALUE(BMCServiceDesk__Actual_Start_Date__c))&" 13:00:00") - BMCServiceDesk__Actual_Start_Date__c,0)) - DATE(1900,1,8),7) +
MIN(1,24/9 * (MOD(BMCServiceDesk__Actual_Start_Date__c +
MAX(DATETIMEVALUE(TEXT(DATEVALUE(BMCServiceDesk__Actual_Start_Date__c))&" 13:00:00") - BMCServiceDesk__Actual_Start_Date__c,0) - DATETIMEVALUE('1900-01-08 13:00:00'), 1))))) )

This fails to correctly calculate business hours when the start and end are both outside business hours (example: start = 4:45 AM, end = 7:15 AM, it shows “9.00” instead of “0.00”

If interested, here's all my test cases:
(1) Start and end on same business day, both before business hours
(2) Start and end on same business day, start before business hours and end within morning business hours
(3) Start and end on same business day, start before business hours and end within afternoon business hours
(4) Start and end on same business day, both within morning business hours
(5) Start and end on same business day, start within morning business hours and end within afternoon business hours
(6) Start and end on same business day, both within afternoon business hours
(7) Start and end on same business day, start within afternoon business hours and end after business hours
(8) Start and end on same business day, both after business hours

(9) - (16) Then repeat (1) – (8), but change the end date to one business day later, so timeframe encompasses 2 business dates
(17) Start on Friday during afternoon business hours, end on Monday during morning business hours
(18) Start on Friday during afternoon business hours, end on Sunday
(19) Start and end on Saturday
(20) Start on Saturday, end on Sunday
Travis ThompsonTravis Thompson
@Dan Bontrager The Failures you outline in your testing for each formula, were those the only specific failure points for the specified formulas?  For example, formula 3, does it ONLY fail when the start and end are each outside business hours?  If that is the case, I think I will be able to use that formula without issue and thank you for posting it!
 
Travis ThompsonTravis Thompson

I am trying to use this formula (#3 from Dan above), I have adjusted it for 8am-5pm EST (9hr days).  Using the variables LastModifiedDate as the Start Date/Time and Now() as the End Date/Time but I am getting the error: "Compiled formula is too big to execute (35,768 characters). Maximum size is 5,000 characters (Related field: Formula)"  I am trying to show in minutes the amount of time since a case has been modified, excluding time that is outside of business hours.  

9 *((5 * FLOOR((DATEVALUE(NOW()) - DATE(1900,1,9))/7) + MIN(5, MOD(DATEVALUE(NOW()) - DATE(1900,1,9),7) + MIN(1,24/9 * (MOD(NOW() - DATETIMEVALUE('1900-01-08 12:00:00'), 1))) )) - (5 * FLOOR((DATEVALUE(LastModifiedDate + MAX(DATETIMEVALUE(TEXT(DATEVALUE(LastModifiedDate))&" 12:00:00") - LastModifiedDate,0)) - DATE(1900,1,9))/7) + MIN(5,MOD(DATEVALUE(LastModifiedDate + MAX(DATETIMEVALUE(TEXT(DATEVALUE(LastModifiedDate))&" 12:00:00") - LastModifiedDate,0)) - DATE(1900,1,9),7) + MIN(1,24/9 * (MOD(LastModifiedDate + MAX(DATETIMEVALUE(TEXT(DATEVALUE(LastModifiedDate))&" 12:00:00") - LastModifiedDate,0) - DATETIMEVALUE('1900-01-08 12:00:00'), 1))))) )

Any ideas on how to resolve this error?
"Compiled formula is too big to execute (35,768 characters). Maximum size is 5,000 characters (Related field: Formula)" 

Dan BontragerDan Bontrager
@Travis Thompson yes, the failures I outline were only for each specified formula the failure is listed by.  So for example, formula 3 only fails when the start and end are each outside business hour.

I am not sure how to resolve the error you are seeing.
Nicole YoungNicole Young
Was excluding holidays ever addressed in any of these formulas?
Claire SunderlandClaire Sunderland
Nicole I wasn't able to do this. I had to move it to code in order to actually reference Organization Business Hours with Holidays included. 

If you don't want to do that you'll have to individually exclude all Holidays in the formula probably
TJ BTJ B
Any update on resolution for the formula adding hours? My hours are 8am-8pm. The formula stops and calculates correctly if it is after 8pm up until 1230am. At 1am it just adds 12 hours. 
Renee NicholusRenee Nicholus
Update:
I have been through Salesforce Support and Admin Assist, and I am now being told this is not possible through the Setup menu, and we need to use Apex.  

Did anyone get a different answer from support or admin assist?
Greg GuevarraGreg Guevarra
Hi Everyone, 

Seems like this has been an ongoing concern and so would like to share what I was able to come up with and hopefully this answers the query that started this thread.

Here is the formula as follows:

IF
   (DATEVALUE( DATE1__c ) = DATEVALUE( DATE2__c ),


   IF( HOUR(TIMEVALUE(DATE2__c)) >= 9, 9, HOUR(TIMEVALUE(DATE2__c))) -
   IF( HOUR(TIMEVALUE(DATE1__c)) <= 1, 1, HOUR(TIMEVALUE(DATE1__c))) ,


   ((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)   
   +IF( HOUR(TIMEVALUE(DATE2__c)) >= 9, 9, HOUR(TIMEVALUE(DATE2__c))) -
     IF( HOUR(TIMEVALUE(DATE1__c)) <= 1, 1, HOUR(TIMEVALUE(DATE1__c)))


A general explanation on how this computes/works (hoping that I can explain it properly for everyone's understanding). You'll notice the formula consist of three groupings, the classic IF-THEN-ELSE statement. The "IF" section (1st grouping) checks if the dates on the two datetime fields are the same and if so, it'll follow the computation on the "THEN" section (2nd grouping). If the two datetime fields are not the same then, it'll instead do the computation on the "ELSE" section (3rd grouping).


A detailed explanation of the groupings so you can adjust it based on your business requirements:

1st Grouping: You simple asks the system to compare the date value only on DATE1__c with the date value on DATE2__c


2nd Grouping:
(Note: Salesforce recognizes date and time in UTC timezone, the 9:00AM UTC being referenced below is equal to 5PM Manila Time which is the end of my business hour and the 1:00AM UTC is equal to 9AM Manila Time which is the start of my business hour. You will need to change the 9:00AM UTC and 1:00AM UTC to whatever is the equivalent of 5PM and 9AM in your timezone or whatever is the equivalent of the end and start of your business hour in UTC timezone)

- 1st line checks if the hour value on the DATE2__c field is greater than or equal to 9:00AM UTC, if its greater than 9:00AM UTC then it'll always return 9:00AM UTC however if its equal to or less than 9:00AM UTC then it'll return whatever value is on the DATE2__c field
- 2nd line checks  if the hour value on the DATE1__c field is less than or equal to 1:00AM UTC, if its less than 1:00AM UTC then it'll always return 1:00AM UTC however if its equal to or greater than 1:00AM UTC then it'll return whatever value is on the DATE1__c field
- The 1st line then gets substracted with the 2nd line and display the result in your business hours field


3rd Grouping:
(Note: this group is a bit tricky to explain, so you'll need to figure out some of it on your own)

- 1st line up to the 4th line actually finds/computes the number of business days between DATE2__c and DATE1__c
- 5th line actually multiplies the resulting computation of the 1st to 4th line to the number 8 with the understanding that every 1 business day that elapsed is equivalent to 8 hours, hence the number 8. If you have a 9 hour business hour then change it 8 to 9 accordingly
- 6th and 7th line adds up to the resulting value from the 5th line by taking in to consideration the actual time value difference between DATE2__c and DATE1__c fields. This line also uses the same logic on the 2nd grouping above which makes sure to always give a constant start time or end time in the event the actual start time or end time falls outside the defined business hours.


Additional Notes:
- This formula is not up to the "seconds" or "minutes" accurate as it only computes up to the hour difference (doing so will just make the formula complex but feel free to share here if you were able to make it that accurate)
- There might be some flaws that I haven't notice yet so you need to test this from your end and see if it works for you
- This formula takes into consideration business days which makes sure weekends are not included in the computation

Hope this help!
Parmanand SheteParmanand Shete
(5 * FLOOR( ( DATEVALUE(End_Date__c) - DATE( 1900, 1, 8) ) / 7 ) + MIN( 5, MOD( DATEVALUE(End_Date__c) - DATE( 1900, 1, 8), 7 ) ) )
 - 
 (5 * FLOOR( ( DATEVALUE(Start_Date__c) - DATE( 1900, 1, 8) ) / 7 ) + MIN( 5, MOD( DATEVALUE(Start_Date__c) - DATE( 1900, 1, 8), 7 ) ) )
Raviraj NallapoolaRaviraj Nallapoola
HI Everyone,

Can any one help me on below secenarioe

1. If for some reason ' End" is set to as datetime before working hours at the same day as ' Start' received value is wrong (always '8')
See example:
Clearing Start 18/09/2019 00:29
Clearing End 18/09/2019 07:29
Last Clearing Duration 8

It should be zero 

Below is my formula field 

IF(AND(ISNULL(Clearing_End__c),ISNULL(Start__c)), 0, 9 *((5*FLOOR((DATEVALUE(IF( ISNULL(End__c) , NOW(), Clearing_End__c))-DATE(1900,1,8))/7)
+
MIN(5, MOD(DATEVALUE(IF( ISNULL(End__c) , NOW(), End__c))-DATE(1900,1,8),7)+ MIN(1,24/9*(MOD(IF( ISNULL(End__c) , NOW(), End__c)-DATETIMEVALUE('1900-01-08 07:00:00'), 1))) ))
- (5*FLOOR((DATEVALUE(Start__c + MIN(DATETIMEVALUE(TEXT(DATEVALUE(Start__c))&" 07:00:00")-Start__c,0))- DATE(1900,1,8))/7)+ MIN(5, MOD(DATEVALUE(Start__c+MIN(DATETIMEVALUE(TEXT(DATEVALUE(Start__c))&" 07:00:00")- Start__c,0))-DATE(1900,1,8),7)+ MIN(1,24/9*(MOD(Start__c + MAX(DATETIMEVALUE(TEXT(DATEVALUE(Start__c))&" 07:00:00")- Start__c,0)-DATETIMEVALUE('1900-01-08 07:00:00'), 1)))))))
Benjamin HoganBenjamin Hogan

Not sure if anyone is interesting in using Apex to achieve this, but if so, the solution is very easy.

All you have to do is set up your business hours and holidays in Setup>Business Hours, then write a class that calulates the business hours between two datetimes by calling the BusinessHours class's diff() method.

Once you have this class written, you can call this class in any Trigger you write for updating a number field that holds the value of the elapsed business hours. Happy to provide the class if anyone needs it.

I like the ease of using the formulas provided, but all the ones I have found only round to the nearest hour, and sometimes they are so long you have to break them up into separate formulas.

Tomasz KoziolTomasz Koziol

Hi Benjamin,

Would you mind sharing your apex class?
It would be very helpful!
 

Benjamin HoganBenjamin Hogan
Tomasz, 

Absolutely, see below. For this to work, you have to set up your business hours in Setup > Business Hours. Then the GetElapsedBusinessHours method simply calls the BusinessHours' diff() method.

You can disregard the first two methods if desired, as they don't take business hours into account. In your trigger, you will call either the GetElapsedBusinessHours() method or the GetElapsedBusinessHoursSince() method. The first requires a start and end datetime, whereas the latter only requires the start datetime.

Lastly, it has been some time since I wrote this class, and do not claim ownership. I believe I borrowed it and updated it to fit my purpose.
public with sharing class ElapsedTime {
        
    public static Decimal GetElapsedHours(Datetime startTime, Datetime endTime) {
        if(startTime == null || endTime == null || startTime == endTime) {
            return 0;
        } else {
            Decimal elapsedMilliseconds = endTime.getTime() - startTime.getTime();
            Decimal elapsedSeconds = elapsedMilliseconds / 1000;
            Decimal elapsedMinutes =  elapsedSeconds / 60;
            Decimal elapsedHours = elapsedMinutes / 60;
            
            // Don't return negative values.
            if(elapsedHours < 0){
                return 0;
            } else {
                return elapsedHours;
            }
        }
    }

    public static Decimal GetElapsedHoursSince(Datetime startTime) {
        Decimal elapsedHours = GetElapsedHours(startTime, System.now());
        return elapsedHours;
    }
    
    public static Decimal GetElapsedBusinessHours(Datetime startTime, Datetime endTime) {
    BusinessHours bh = [SELECT Id FROM BusinessHours WHERE IsDefault=true];
        if(startTime == null || endTime == null || startTime == endTime) {
            return 0;
        } else {
            Decimal elapsedBusinessMilliseconds = BusinessHours.diff(bh.Id, startTime, endTime);
            Decimal elapsedBusinessSeconds = elapsedBusinessMilliseconds / 1000;
            Decimal elapsedBusinessMinutes =  elapsedBusinessSeconds / 60;
            Decimal elapsedBusinessHours = elapsedBusinessMinutes / 60;
        // Don't return negative values.
            if(elapsedBusinessHours < 0){
                return 0;
            } else {
                return elapsedBusinessHours;
            }
        }
    }
    
    public static Decimal GetElapsedBusinessHoursSince(Datetime startTime) {
        Decimal elapsedBusinessHours = GetElapsedBusinessHours(startTime, System.now());
        return elapsedBusinessHours;
    }
}

 
Brayan Ibañez BalladaresBrayan Ibañez Balladares
I would like to contribute to the community, so after seeing everyone's formulas I have obtained the definitive formula to calculate business hours from 8AM - 6PM, without considering Saturdays and Sundays, at the established time of: (GMT-05: 00) Peru Standard Time (America / Lima).

SURA_DateTimeAssignment__c = Start date and time.
DateTime_HourTest__c = End date and time.

NOTE: It does not count to enter the end time less than the start time, that case would never happen in real life.


IF(DATEVALUE( DateTime_HourTest__c ) = DATEVALUE( SURA_DateTimeAssignment__c ),

(
CASE(HOUR(TIMEVALUE(DateTime_HourTest__c)),
13,13,
14,14,
15,15,
16,16,
17,17,
18,18,
19,19,
20,20,
21,21,
22,22,
23,23,
IF(HOUR(TIMEVALUE(DateTime_HourTest__c))>=5 && HOUR(TIMEVALUE(DateTime_HourTest__c))<13,
13,IF(HOUR(TIMEVALUE(DateTime_HourTest__c))<6,23,0))
) - 
CASE(HOUR(TIMEVALUE(SURA_DateTimeAssignment__c)),
13,13,
14,14,
15,15,
16,16,
17,17,
18,18,
19,19,
20,20,
21,21,
22,22,
23,23,
IF(HOUR(TIMEVALUE(SURA_DateTimeAssignment__c))>=5 && HOUR(TIMEVALUE(SURA_DateTimeAssignment__c))<13,
13,IF(HOUR(TIMEVALUE(SURA_DateTimeAssignment__c))<6,23,0))
)
)

,

((5 * FLOOR((DATEVALUE (DateTime_HourTest__c) - DATE (1900, 1, 7)) / 7) + MIN (5, MOD (DATEVALUE (DateTime_HourTest__c) - DATE (1900, 1, 7), 7))) -
(5 * FLOOR((DATEVALUE (SURA_DateTimeAssignment__c) - DATE (1900, 1, 7)) / 7) + MIN (5, MOD (DATEVALUE (SURA_DateTimeAssignment__c) - DATE (1900, 1, 7), 7))))*10



(
CASE(HOUR(TIMEVALUE(DateTime_HourTest__c)),
13,13,
14,14,
15,15,
16,16,
17,17,
18,18,
19,19,
20,20,
21,21,
22,22,
23,23,
IF(HOUR(TIMEVALUE(DateTime_HourTest__c))>=5 && HOUR(TIMEVALUE(DateTime_HourTest__c))<13,
13,IF(HOUR(TIMEVALUE(DateTime_HourTest__c))<6,23,0))
) - 
CASE(HOUR(TIMEVALUE(SURA_DateTimeAssignment__c)),
13,13,
14,14,
15,15,
16,16,
17,17,
18,18,
19,19,
20,20,
21,21,
22,22,
23,23,
IF(HOUR(TIMEVALUE(SURA_DateTimeAssignment__c))>=5 && HOUR(TIMEVALUE(SURA_DateTimeAssignment__c))<13,
13,IF(HOUR(TIMEVALUE(SURA_DateTimeAssignment__c))<6,23,0))
)
)

)

Please, if anyone finds any errors with the formula I provided, let them know. Thank you very much.
Adnan SheikhAdnan Sheikh
Code below worked for me.  Working hours are Monday-Friday 9:00amn - 6:00pm EST

IF(DATEVALUE(Start_Time__c) = DATEVALUE(End_Time__c),
IF(
OR(
weekday( DATEVALUE(Start_Time__c)) = 1,
weekday( DATEVALUE(Start_Time__c)) = 7
),
0,
(
IF(
OR(
HOUR(TIMEVALUE(End_Time__c)) >= 23,
HOUR(TIMEVALUE(End_Time__c)) <= 5
),
TIMEVALUE("23:00:00.000"), TIMEVALUE(End_Time__c)
) -
IF(
TIMEVALUE(Start_Time__c) < TIMEVALUE("14:00:00.000"), TIMEVALUE("14:00:00.000"), TIMEVALUE(Start_Time__c)
)
)
)/3600000,

(((5 * ( FLOOR( ( DATEVALUE(End_Time__c) - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( DATEVALUE(End_Time__c)- DATE( 1900, 1, 8), 7 ) ) ) -
(5 * ( FLOOR( ( DATEVALUE(Start_Time__c)- DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( DATEVALUE(Start_Time__c)- DATE( 1900, 1, 8), 7 ) ) )) - 1)
* 9 +

((TIMEVALUE("23:00:00.000") - (IF(
TIMEVALUE(Start_Time__c) < TIMEVALUE("14:00:00.000"), TIMEVALUE("14:00:00.000"), TIMEVALUE(Start_Time__c)
)))/3600000) +

((IF(
OR(
HOUR(TIMEVALUE(End_Time__c)) >= 23,
HOUR(TIMEVALUE(End_Time__c)) <= 5
),
TIMEVALUE("23:00:00.000"), TIMEVALUE(End_Time__c)
) - TIMEVALUE("14:00:00.000"))/3600000)
)
Cat SheltonCat Shelton
It's hugely apparent that there's a gap with easy Business Hours calculations, I've been battling with it for a few days now and I still have issues with Negative values if the two dates fall on the same day, but one is outside business hours and it's hugely frustrating. 

I have raised an Idea on this so please upvote it! https://trailblazer.salesforce.com/ideaView?id=0874V000000ciMCQAY

It seems a bit odd that you specify business hours in set up and yet these cannot be referenced in a simple way within formulas :(

If anyone is interested the formulas I had assistance with are visible on https://trailblazers.salesforce.com/answers?id=9064S000000DOGL and https://trailblazers.salesforce.com/answers?id=9064S000000DNzKQAW
Cat SheltonCat Shelton
Hi all, after my reply yesterday I have found a solution which may work. 

https://www.professorflow.com/post/salesforce-business-hours

You can install a package which includes an Apex class that you can then use in Flow to calculate business hours and it works brilliantly. 
Val SouthernVal Southern
I was asked to calculate the business hours from a date/time value (updated on change of case status) to the current date/time.
With the timestamp already in place along with business hours set up as GMT Mon-Fri 08:30-17:30, I was able to exclude weekends (weekdays 7 and 1 being Saturday & Sunday) and time outside business hours from the calculation using business hours.
This assumes that Mon-Fri have the same business hours and I was able to reference Monday from the case's related business hours in the formula for all working days.
You can replace 'Status_Last_Changed__c' with any starting date/time and 'NOW()' with another ending datetimestamp value as required:

IF(DATEVALUE( Status_Last_Changed__c ) = DATEVALUE(NOW()),
IF(
OR(
weekday( DATEVALUE(Status_Last_Changed__c )) = 1,
weekday( DATEVALUE(Status_Last_Changed__c )) = 7
),
0,
(
IF(
OR(
HOUR(TIMEVALUE(now())) >= HOUR( BusinessHours.MondayEndTime ),
HOUR(TIMEVALUE(now())) <= HOUR( BusinessHours.MondayStartTime )
),
BusinessHours.MondayEndTime, TIMEVALUE(now())
) -
IF(
TIMEVALUE(Status_Last_Changed__c ) < BusinessHours.MondayStartTime, BusinessHours.MondayStartTime, TIMEVALUE(Status_Last_Changed__c )
)
)
)/3600000,

(((5 * ( FLOOR( ( DATEVALUE(now()) - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( DATEVALUE(now())- DATE( 1900, 1, 8), 7 ) ) ) -
(5 * ( FLOOR( ( DATEVALUE(Status_Last_Changed__c )- DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( DATEVALUE(Status_Last_Changed__c )- DATE( 1900, 1, 8), 7 ) ) )) - 1)
* 9 +

((BusinessHours.MondayEndTime - (IF(
TIMEVALUE(Status_Last_Changed__c ) < BusinessHours.MondayStartTime, BusinessHours.MondayStartTime, TIMEVALUE(Status_Last_Changed__c )
)))/3600000) +

((IF(
OR(
HOUR(TIMEVALUE(now())) >= HOUR(BusinessHours.MondayEndTime),
HOUR(TIMEVALUE(now())) <= HOUR(BusinessHours.MondayStartTime)
),
BusinessHours.MondayEndTime, TIMEVALUE(now())
) - BusinessHours.MondayStartTime)/3600000)
)

I hope this helps! Please mark as best answer if it helps becasue it will help others to find it more easily :)
Julie CurryJulie Curry
We are trying to implement this with the formula (pasted below) that Renee Nicholus provided from Salesforce. We have staff around the world and want to apply their working hours in their timezone. Leveraging a 3rd party application where we can insert the user schedule, we have populated on the User object the user's start time in GMT and their number of work hours per day that we are inserting in the formula. To get these numbers it looks like the developer supporting us added Apex for the user record. With this, we still get incorrect numbers returned. Specifically, the issue we are trying to resolve right now is if the submission is after business hours and the response is before busines hours the following business day then the number returned is the user's number of work hours per day. I'm running out of ideas regarding how to troubleshoot. I'm not a developer. I'm not sure what else to try. I guess right now at the very least I'd like to understand whether this can ever work the way we want or am I just wasting my time. Thanks in advance for your help!


9 *((5*FLOOR((DATEVALUE(Stop_Time__c)-DATE(1900,1,8))/7) + MIN(5, MOD(DATEVALUE(Stop_Time__c)-DATE(1900,1,8),7)+ MIN(1,24/9*(MOD(Stop_Time__c-DATETIMEVALUE('1900-01-08 13:00:00'), 1))) )) - (5*FLOOR((DATEVALUE(Start_Time__c + MAX(DATETIMEVALUE(TEXT(DATEVALUE(Start_Time__c))&" 13:00:00")-Start_Time__c,0))- DATE(1900,1,8))/7)+ MIN(5, MOD(DATEVALUE(Start_Time__c+MAX(DATETIMEVALUE(TEXT(DATEVALUE(Start_Time__c))&" 13:00:00")-Start_Time__c,0))-DATE(1900,1,8),7)+ MIN(1,24/9*(MOD(Start_Time__c + MAX(DATETIMEVALUE(TEXT(DATEVALUE(Start_Time__c))&" 13:00:00")-Start_Time__c,0)-DATETIMEVALUE('1900-01-08 13:00:00'), 1))))) )
Val SouthernVal Southern
Julie Curry,
I would suggest following the guidance for setting up business hours and applying to cases for each service centre here:
https://help.salesforce.com/articleView?id=customize_supporthours.htm&type=5
...and then try using the detail from my post (the one before yours, above. I've not tried across timezones however, since it leverages the case's related business hours instead of hard coded time values, could potentially work for you.
Best of luck!
Julie CurryJulie Curry
@Val Southern, thank you for your response. We're using this with leads which I think creates more of an issue.
Benjamin HoganBenjamin Hogan
Anyone who is still having issues with this, email me at benjamin.hogan@southwire.com. I will walk you though my answer (above) to accomplish with Apex and setting up your business hours in setup. It is very simple and not sloppy like the long formulas offered in this thread. This is very easy to accomplish and can be easily adjusted simply by changing business hours in setup and even adding holidays in setup.
Tai RahmanTai Rahman
@Val Southern Thanks so much for your formula. Did you notice it breaks when the end date is outside of business hours though? I've seen it over shoot in those instances. 
 
Saurabh GuptaSaurabh Gupta
Hi all,

Sorry for any language mistake.

I was also looking for the answer of this ques for one of my task requirements and after seeing so many helpful answers and reply's , I was able to build something after some tweaking which may could help someone.

FORMULA:

ROUND(540*(
(5*FLOOR(( DATEVALUE(End_Time__c)-DATE(1996,01,01))/7) +
MIN(5,
(MOD(DATEVALUE(End_Time__c)-DATE(1996,01,01), 7) - IF(TIMEVALUE("05:00:00.000") <= TIMEVALUE(End_Time__c) && TIMEVALUE(End_Time) <= TIMEVALUE("14:59:59.000"),1,0)) +
MIN(1, 1440/540*(MOD(End_Time__c-DATETIMEVALUE('1900-01-08 15:00:00'), 1)))
))
-
(5*FLOOR((DATEVALUE(Start_Time__c)-DATE(1996,01,01))/7) +
MIN(5,
(MOD(DATEVALUE(Start_Time__c)-DATE(1996,01,01), 7) -IF(TIMEVALUE("05:00:00.000") <= TIMEVALUE(Start_Time__c) && TIMEVALUE(Start_Time__c) <= TIMEVALUE("14:59:59.000"),1,0)) +
MIN(1, 1440/540*(MOD(Start_Time__c-DATETIMEVALUE('1996-01-01 15:00:00'), 1)))
))
), 0)

Here working hours are from 10:00am est to 7:00pm est which is 9hours/day(540minutes/day) from Monday to Friday
I have put IF condition to check whether the Start Time OR EndTime occur after OR before business hours so to take extra day out of calculation otherwise which can mislead our calculation.
TIMEVALUE("14:59:59.000") is because day start from 10:00am and convert it into your UTC timezone.
And also you need to change (TIMEVALUE which is in IF Conditon)  according daylight saving if it follows in your scenario.

Hope it may helps.
And please let me know if I have done any mistake.