Calculate working hours for a Helpdesk SLA report

**excelitps** · 01-13-2015, 10:39 AM

Hi All,

Having spent a few hours trawling through forums and experimenting, I come humbly to you experts! I did consider putting this in the basic/excel general subforum, apologies if I chose poorly by going for this one.

I have the following fields:

A2:A100 - date/time that a helpdesk ticket opened
B2:B100 - date/time that the same tickets were closed (so A2 is open date and b2 is close date of that single ticket)
D2 - start time of each working day (8.30)
E2 - end time of each working day (17:00)
G2:G5 - holidays

I want to see the time difference between start and end date (columns A and B) in HH:MM

I have been trying with the NETWORKDAYS function, which seems to work for most dates, but falls over on the odd one e.g. where the open and close date/times are outside of the normal working period e.g. opened at 9pm and closed at 9.10pm on the same day, or opened on 26th Dec 2014 (which is included as part of the holidays range)

Your help is greatly anticipated and appreciated!!

Thanks
Mark

**daddylonglegs** · 01-13-2015, 10:46 AM

Hello Mark, try this formula for row 2 copied down

=(NETWORKDAYS(A2,B2,G$2:G$5)-1)*(E$2-D$2)+IF(NETWORKDAYS(B2,B2,G$2:G$5),MEDIAN(MOD(B2,1),D$2,E$2),E$2)-MEDIAN(NETWORKDAYS(A2,A2,G$2:G$5)*MOD(A2,1),D$2,E$2)

Format result cell with this custom format

=[h]:mm

**Kaper** · 01-13-2015, 10:50 AM

have yoiu checked this:
http://www.excelforum.com/excel-form...end-hours.html
and this: http://www.excelforum.com/non-englis...werkdagen.html

**excelitps** · 01-13-2015, 10:54 AM

Hello there, wow quick response! Thank you - I'll try it and let you know

**excelitps** · 01-13-2015, 10:55 AM

Hi Kaper, I did have a look at those, but I am afraid my ignorance prevented me from getting them to work as I needed. Daddylonglegs has kindly provided a formula which I am testing now

Thanks
Mark

**excelitps** · 01-13-2015, 11:02 AM

Hi DaddyLongLegs, I am getting some results which I can't understand. For example, with a start and end date of 03/12/2014 13:57 10/12/2014 12:43 respectively, the formula gives me a result of 17:16 which I take to be 17 hours 16 mins? that dates mentioned don't include any of the 'holiday' ones in the G column so I am not sure where it is getting this figure from?

Thanks
Mark

**daddylonglegs** · 01-13-2015, 11:41 AM

Hello Mark,

that sounds like a formatting issue. If you format as hh:mm then you will only see times up to 23:59, any time above that will just show the leftover portion after you have taken out multiples of 24 hours, so, as per my first reply....