Calculate Transaction Time Over Days
 

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.

Calculate Transaction Time Over Days
 

Hi Denny,

Look he

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Denny Knepper" <Denny wrote in message
...
|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.
|

Calculate Transaction Time Over Days
 

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.

--
Denny Knepper
Management Analyst
PA Dept of Transportation


"Niek Otten" wrote:

Hi Denny,

Look he

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Denny Knepper" <Denny wrote in message
...
|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.
|

Calculate Transaction Time Over Days
 

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

"Denny Knepper" wrote in message
...
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.

--
Denny Knepper
Management Analyst
PA Dept of Transportation


"Niek Otten" wrote:

Hi Denny,

Look he

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Denny Knepper" <Denny wrote in
message
...
|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.
|