Hours worked

[egandolfi]

Hi all,
I am basically Excel illiterate so forgive my lack of knowledge. I have been generating help ticket reports within Excel. Very basic stuff. What I need to do is calculate the time a ticket has been opened based on only business hours. I have the open date in cell E2 and Closed Date in cell F2. I'd like to calculate the time in based on our business hours from 8:00am ET to 6:00pm ET. I did some searching and found some formulas on the Internet but they don't seem to work. They reference NETWORKDAYS and NETWORKDAYS.INTL. Could someone help me come up with a formula for this specific situation.

I just need it to take the open date and close date and calculate the time based on monday - friday 8:00am - 6:00pm. I am nopt worried about holidays at this time I just want to eliminate nights and weekends.

Thank you SO MUCH in advance for your help!

Regards,
Eddie

[daddylonglegs]

Hello Eddie, I assume E2 and F2 include dates and times. If these are always within your working hours then try this formula for total hours

=(NETWORKDAYS(E2,F2)-1)*("18:00"-"8:00")+MOD(F2,1)-MOD(E2,1)

Custom format result cell as [h]:mm

If your open/close times might be any time including outside the business hours, e.g. at weekends or before 08:00 then try this version

=(NETWORKDAYS(E2,F2)-1)*("18:00"-"8:00")+IF(NETWORKDAYS(F2,F2),MEDIAN(MOD(F2,1),"8:00","18:00"),"18:00")-MEDIAN(NETWORKDAYS(E2,E2)*MOD(E2,1),"8:00","18:00")

[egandolfi]

Thank you so much! Is there away to have this go right to total minutes rather than hours:minutes?

[daddylonglegs]

If you want minutes then instead of [h]:mm format use [m]

[egandolfi]

Thank you again!