*Urgent Help If Possible* Subtracting date/time accounting for work hours and weekends?

This is an emergency of sorts. I was just made aware of a metric I needed to include in a report that needs to go out today and I have no idea how to do it with what I know so far. I've checked over the forums and see other similar questions but I've tried the example solutions without success. I need to find the total time a ticket was opened but only counting work hours (7:30AM to 5:30PM) and not counting any weekend time. I'm attaching a sample document to this and cell E2 contains my attempt to modify a solution example I found in this forum. I'm not sure what I'm doing wrong. Essentially cell C2 is "date/time opened" and D2 is "date/time closed" and I would like E2 to display what the total hours are with the considerations listed above. Any ideas? I'm up a creek if I can't get this before I leave so any guidance would be extremely helpful.

Hello Michael,

This formula should do what you want

=(NETWORKDAYS(C2,D2)-1)*("17:30"-"7:30")+IF(NETWORKDAYS(D2,D2),MEDIAN(MOD(D2,1),"7:30","17:30"),"17:30")-MEDIAN(NETWORKDAYS(C2,C2)*MOD(C2,1),"7:30","17:30")

Custom format result cell as [h]:mm

Hi and welcome to the forum

only counting work hours (7:30AM to 5:30PM)

what do you want to do with times outside of that range? D11 = 1/7/2013 11:33:21 PM I didnt see any Start timed earlier than 7:30, but could there be?