Hi All,

This is my first post, I hope I am following the rules correct and am not creating an issue.

I have looked up differences in dates and times and cannot get the exact answer I require.

My sheet is set up as such.

A1=Log on date and time, 22/01/2015 09:00 (DD/MM/YYYY hh:mm)
B1 = Response date and time, 03/02/2015 10:30 (DD/MM/YYYY hh:mm)
C1= Working day start time, 08:30 (hh:mm)
D1=Working day finish time, 17:00 (hh:mm)

I am using the formula below, in F1, formatted as [H]:mm to return the numbers of working hours between the two dates. This is for complaint response times taking into account working hours.

=(INT(B1)-INT(A1))*(D1-C1)+MEDIAN(MOD(B1,1),C1,D1)-MEDIAN(MOD(A1,1),C1,D1)

I then am using the formula below, in H1, formatted as general to return the number of days, hours and minutes allowing for the length of the working day.

=INT(F1)&" d "&TEXT(F1,"h"" hrs ""m"" mins""")

What I am struggling with is how to add a function to ensure non working days are not counted. I cannot use network days as members of my team work weekends and have other days off in the week.

Also I would like to include cells that contain other non working days such as bank holidays and annual leave; these could be entered as dates between L1:Z1.

Sometimes the responses can go over 31 days!!!

Many thanks in advance, I have found this site very useful in the past.

Rob.