SLA Setup in Excel

Oh man! Ad-blocking software has been detected! :'(

This website is run by the community, for the community... and it needs advertisements in order to keep running. Blocking our ads means your killing our stats!
Please disable your ad-block, or become a premium member to hide all advertisements and this notice.

Hi,


I am trying to setup a SLA spreadsheet with data from our helpdesk system.


Cell A – 10/10/2012 08:00
Cell B - 10/10/2012 15:00


1. Working hours 08:00 to 18:00 weekdays only
2. Calls are logged at anytime during day/night


I have a formula which I got of the internet


=(NETWORKDAYS(C3,D3)-1)*("18:00"-"8:00")+MOD(D3,1)-MOD(C3,1)


Which works if the days are in the same month and within the above time frame


What I am look for help with is…


I need to work out the hours open if the call is logged on the last day of the month and the next working day is start of the next month(As this can be within the SLA) and also how to stop it from calculating outside the working hours.


As I am not that clued up on Excel was wondering if anyone could point me in the correct direction. hope this makes sense.


Thank you