time/date tracking only during business hours

i work dispatch at a company and we're trying to track how long it takes for a technician to arrive on-site. my worksheet currently includes the date of call-in (E1) , time of call-in (F1), Service Date (G1), Service Time (H1), among many other field. the easy solution is to use the formula (G1+H1)-(E1+F1), and use the answer as the total amount of time, but the problem is if a customer call in at 3:00 PM and we service them at 9:00 AM the following day, it looks like it took 18 hours to arrive on-site, when in reality, since we close at 5:00 PM and open at 9:00 AM, we only took 2 hours to arrive on-site. is there any formula i can write to account for non-business hours and weekends, or is this a bit above what i should expect from excel?
thanks in advance for any help!

Assuming the call in and service times are always within your business hours i.e. Monday to Friday 9 - 5 try this formula

=(NETWORKDAYS(E1,G1)-1)/3+H1-F1

format as [h]:mm

Note: NETWORKDAYS requires Analysis ToolPak addin. To enable, Tools > add-ins > tick "Analysis ToolPak" box, you can also exclude holidays if you have a list of holiday dates.....

that works fantastic, thank you so much! you mentioned i can exclude holidays... how is this possible, and for my own future reference, is it possible to change the hours of the day (eg. 8am-5pm instead of 9am-5pm)?
again, thanks for your help!

The generic formula is

=(NETWORKDAYS(E1,G1)-1)*("17:00"-"09:00")+H1-F1

where 09:00 is daily start time and 17:00 daily finish time, or you can simply put your daily start and finish times in two cells, e.g. M1 & M2 and use the formula

=(NETWORKDAYS(E1,G1)-1)*(M$2-M$1)+H1-F1

To exclude holidays you can list these somewhere on the spreadsheet, e.g. Z1:Z10 and use

=(NETWORKDAYS(E1,G1,Z$1:Z$10)-1)*(M$2-M$1)+H1-F1

Thanks so much for your prompt help. You've made my job a whole lot easier, and more importantly, you've made my boss happy.

is it possible, with my new data from the help above, to formulate how many instances of calls over 4 hours i have relative to the type of call. to clarify, i have a column of times (amount of time, not time of day) (M1) and a column of job codes (S=service, R=return call, D=delivery) (L1).
i would like to calculate how many instances of time (M1) exceed four hours and also have a job code (L1) of "S"

Originally Posted by daddylonglegs

The generic formula is

=(NETWORKDAYS(E1,G1)-1)*("17:00"-"09:00")+H1-F1

where 09:00 is daily start time and 17:00 daily finish time, or you can simply put your daily start and finish times in two cells, e.g. M1 & M2 and use the formula

=(NETWORKDAYS(E1,G1)-1)*(M$2-M$1)+H1-F1

To exclude holidays you can list these somewhere on the spreadsheet, e.g. Z1:Z10 and use

=(NETWORKDAYS(E1,G1,Z$1:Z$10)-1)*(M$2-M$1)+H1-F1

I get a negative time value when the start and end value are within the same day. What can I do to correct for this?

Can you post the exact formula that's giving you problems?

=(NETWORKDAYS(C10,E10)-1)*("19:00"-"06:00")+F10-D10


Column C: Start Date 5/22/09
Column D: Start Time 1:00 PM (in military time format)
Column E: End Date 5/22/09
Column F: End Time 7:00 PM (in military time format)

Does this help?

If I try that formula with that data I get a result of 6:00 as expected, see attached

err, eh hem....I made a silly mistake. I typed 1:00 AM instead on 1 PM. Thanks for your time.

Originally Posted by daddylonglegs

If I try that formula with that data I get a result of 6:00 as expected, see attached

What am I doing wrong in the attached workbook? It seems like the formula is ignoring my hours of operation (6a-7p).

Can I bother you for your help? I can't make the NETWORKDAYS formula work. I attached a sample of the spreadsheet in my post.