Calculate Transaction Time Over Days

[Denny Knepper]

I am trying to calculate how much time it takes, in hours and minutes, to
process a transaction when the start time occurs on one day and time and the
end-time occurs a day or two later. The processing time needs to exclude
weekends and non-working hours.
I have been trying to use the NETWORKDAYS function and have a list of
holidays.
I can get the total elapsed hours between the two days but run into problems
when trying to deduct the non-working hours during the week and the weekends
and holidays.

 

[Niek Otten]

Hi Denny,

Look here:

http://www.sulprobil.com/html/count_hours.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I am trying to calculate how much time it takes, in hours and minutes, to
| process a transaction when the start time occurs on one day and time and the
| end-time occurs a day or two later. The processing time needs to exclude
| weekends and non-working hours.
| I have been trying to use the NETWORKDAYS function and have a list of
| holidays.
| I can get the total elapsed hours between the two days but run into problems
| when trying to deduct the non-working hours during the week and the weekends
| and holidays.
|

 

[Denny Knepper]

Niek:

Thanks for the link, but I was really hoping to avoid using VBA. I don't
know much about it and was hoping there would be some combination of time or
date functions that I could put into a cell or series of cells.

 

[Fred Smith]

Can you approach the problem in the following way:

1. Use Networkdays to get the number of whole days between the two dates
(excluding the start and end days).
2. Multiply this by the number of working hours in the day.
3. Add time worked on the start day (which would be quitting time minus the
start time)
4. Add time worked on the end day (eg, ending time minus workday start
time).

If A1 is Start day/time, and A2 is end day/time, something like this:

=(networkdays(a1,a2)-2)*8+time(17,0,0)-mod(a1,1)+mod(a2,1)-time(8,30,0)

Would this work for you?

Regards,
Fred