Formula for Date and Time duration

A

Adam

Morning All,

I have to work out the time in which a case has taken to resolve on our system.

Now in Excel I have the Case Date (Date opened), Case Time (Time opened), Closure Date, Closure Time.

Does anyone have a formula that works out the Number of Days and Time it has taken to resolve a case? This needs to not include weekends and the working hours 08:30 to 18:30.

This is way beyond my knowledge but I'm sure it can be done, I'm hoping someone has a formula for this already.
 
N

Norman Harker

Evening Adam!

I have
Date Opened in A1
Time Opened in B1
Date Closed in C1
Time Closed in D1

Formula for elapsed time:
=((NETWORKDAYS(A1,C1)-2)*10)/24+TIME(18,30,0)-B1+D1-TIME(8,30,0)
Format [hh]:mm

NETWORKDAYS is an Analysis ToolPak function and needs to be installed
and selected as an Addin.

NETWORKDAYS counts the days inclusive of the start date and end date.
So I need to deduct 2 and multiply by 10 hours to get the number of
hours for the full days. This is divided by 24 to convert to Excel
time which is recorded as a decimal part of a day. Then I add the time
between time opened and knock off time and also add the time between
clock on time and time closed.

It seems to test OK but it's after 18:30 here!
 
A

Adam

Hi Norman,

That almost worked.

If you take the example:

Date opened 02/01/04
Time opened 18:00
Date Closed 03/01/04
Time Closed 18:30

Try your formula made on that it returns 00:30 so it doesn't appear to take into account the days difference. I'd like to see it in hours so this would ideally read 08:30 being 8 hours and 30 mins.

Any ideas please?

This would be so good if I could get this working.
 
N

Norman Harker

Hi Adam!

Thanks for the second post; I was falling into the same trap <vbg>

Good to see it's working OK.

You can add a third argument to NETWORKDAYS, a range that contains
holidays that are also to be excluded.
 
Top