Downtime Report - Get working hours between two date/time stamps less holidays and weekends only counting working hours

[anpage]

Hi there, I am needing help on a formula to calculate the working hours between two date/time stamps - less weekends and holidays that I define. I have searched threads for days and cannot get it to work.

Working Hours (EST): 8:00am - 10:00pm
Weekends: Saturday & Sunday
Holiday: Sheet 1, Column A, Row 1-7 (New Years, MLK, Memorial Day, Independence Day, Labor Day, Thanksgiving, Christmas)

Currently I have the date/time in separate columns (I can put them in the same cell so it could be Opened Date/Time in one Cell and Closed Date/Time in the next cell):
Opened Date: Column E
Opened Time: Column F
Closed Date: Column G
Close Time: Column H


Here is a sample of what my form currently looks like:

A B C D E F G H
Opened Closed
Environment Impact Hours Ticket Date Time Date Time
Test 1 LOW 62.5 HLP0001 9/27/2017 8:00:00 10/3/2017 14:30:00
Test 5 HIGH 3.25 HLP0002 10/4/2017 13:00:00 10/4/2017 16:15:00
Test 7 LOW 3 HLP0003 10/6/2017 8:15:00 10/6/2017 11:15:00


What I need to return is the number of hours our Environment was down for the reported month period. In the example above I am trying to figure out the number of working hours our environment was down in October 2017.

Parameter are below:
Working Hours (EST): 8:00am - 10:00pm
Weekends: Saturday & Sunday
Holiday: Sheet 1, Column A, Row 1-7 (New Years, MLK, Memorial Day, Independence Day, Labor Day, Thanksgiving, Christmas)

Please, Please, Please help!! I have tried numerous formulas and I only get #Value back.