Elapsed time accounting for shift hours and weekends

[ChiefsFan1967]

I'm trying to come up with a formula that will show total time a job is in a department, taking into account a shift working from 7:00 am to 3:30 pm, and discounting weekends. Start/End times are entered as 3/12/20 9:00 AM, result of formula should be hh:mm. I know this involves the networkdays function, but am not knowledgeable enough to come up with the actual formula. This is just part of a larger spreadsheet I have to keep, so I will actually be using this formula for numerous other calculations involving similar Start/End/Totals. I appreciate any help, and apologize in advance for any needed information or specifics I have omitted.

Example: ( column D is just used to indicate dates involving a weekend, not part of calculation)

 

[tyija1995]

Hey, these questions are always tricky with so much criteria going on but try this in cell C2 and drag down the C column:

=IF((A2-ROUNDDOWN(A2,0))<(7/24),8.5,IF((A2-ROUNDDOWN(A2,0))>(15.5/24),0,((15.5/24)-(A2-ROUNDDOWN(A2,0)))*24))+IF(B2-ROUNDDOWN(B2,0)<(7/24),0,IF(B2-ROUNDDOWN(B2,0)>(15.5/24),8.5,(B2-ROUNDDOWN(B2,0)-(7/24))*24))+(NETWORKDAYS(A2,B2)-2)*8.5

 

[ChiefsFan1967]

Hey, these questions are always tricky with so much criteria going on but try this in cell C2 and drag down the C column:

=IF((A2-ROUNDDOWN(A2,0))<(7/24),8.5,IF((A2-ROUNDDOWN(A2,0))>(15.5/24),0,((15.5/24)-(A2-ROUNDDOWN(A2,0)))*24))+IF(B2-ROUNDDOWN(B2,0)<(7/24),0,IF(B2-ROUNDDOWN(B2,0)>(15.5/24),8.5,(B2-ROUNDDOWN(B2,0)-(7/24))*24))+(NETWORKDAYS(A2,B2)-2)*8.5

testing now, I think this will work. will let you know for sure in a little bit.