Calculate Time between 2 dates and if within time frame

**halfpint123** · 04-11-2012, 08:58 AM

Hi

I have a spreadsheet, which has colums of dates - received and sent , both in format - dd/mm/yyyy hh:mm.

I need to calculate the response times, between the received and sent dates. This also needs to exclude weekends & holidays. (struggling as would like to show results in Days/Hours/Minutes).

I then need to know if the item has been sent within time frame (Yes/No answer) . There are differing time frames, depending on the item.

I have creating a reference for the time frames and holidays.

I have been messing about with NETWORKDAYS but can’t get it to work.

I then need to pivot table the data, so I can create average send times for the MS and category column’s

I have attached a blank sheet example, if some one could input the formulas that would be amassing.

Thanks

**DGagnon** · 04-11-2012, 09:07 AM

Try this out, then format as you need

=NETWORKDAYS(D2,E2)+E2-D2-(INT(E2-D2))

**halfpint123** · 04-11-2012, 09:13 AM

Thanks

Just one prob - the response result seems to be a day out, example (larger data set) -

21/11/2011 11:45 21/11/2011 14:02 01 02:17

(should be zero days)

can you tweak the formula?

Thanks

**DGagnon** · 04-11-2012, 09:21 AM

try this

=NETWORKDAYS(D2,E2)+E2-D2-(INT(E2-D2))-1

note: you will also need to add the reference to your holidays into the NETWORKDAYS function

Edit:
here is the update including the holiday reference
=NETWORKDAYS(D2,E2,Holidays!B$2:B$7)+E2-D2-(INT(E2-D2))-1

**halfpint123** · 04-11-2012, 09:27 AM

Cool got that to work and can pivot table the data.

Can you show me how to do the second requet - i.e. has it been sent within time frame?

Thanks

**DGagnon** · 04-11-2012, 09:35 AM

you can use this:
=IF(F2<=VLOOKUP(C2,'Time Frames (working days)'!A:B,2,0),"Yes","No")

note: there is a training space in your Time Frames (working days) sheet, after Info Email that will need to be removed.