Calculate the difference between 2 working days - NETWORKDAYS does not work exactly

Hi

I need to calculate the difference between 2 dates to prove that my team is meeting agreed timescales for producing work.

My current method of working out the timescale is to use the following formula (Column F is 'Date Email Request Received' and Column G is 'Date Quote Sent')

=IF(F5>G5,0,IF(G5-F5=0,0,G5-F5))

The formula removes incorrectly input data where the quote was sent before the request was received.

My problem is that the above formula does not take into account Weekdays and if I use the NETWORKDAYS function then it rounds the time between the 2 dates.

Any help to resolve this would be gratefully received!!

Thanks

James

Hi Kevin

Using the example of quote coming in at 26/04/2013 16:00 and being sent out on 29/04/2013 10:00

The turnaround time using the above formula returns a value of 2.00

Using the original formula it returns 2.75

The actual turnaround time should be 0.75 (excluding weekends)

However if I could turn that into working time between 08:00 and 17:00 then the value should be 0.33 (3 hours work in a 9 hour working day).

Hi Kevin

This works to a point.

However, I think that the median function skews the results.

For example

Date quote comes in 19/12/2012 13:09
Date quote sent out 03/01/2013 11:13

The above formula produces a figure of 3.67

The actual figure should be around 8.5 working days...

Hi Kevin

I have just been messing around with that formula...I think if it is revised to

=((NETWORKDAYS(F4,G4,Sheet1!$B$5:$B$12)-1)+IF(NETWORKDAYS(G4,G4,Sheet1!$B$5:$B$12),MEDIAN(MOD(G4,1),"17:00","08:00"),"17:00")-MEDIAN(MOD(F4,1),"17:00","08:00"))

i.e. removing the *("17:00"-"08:00") against the networkdays then it produces something in the correct ballpark.

I have put holidays into the formula and that bit works.

The formula still doesn't work exactly though

Thanks for your help.