I have two date columns formated like this:

1/22/08 2:19 PM

I need to subract them to get the total time but I need to exclude weekends
and hours not in the normal working day. If the start of the day is 8:30AM
and ends at 5:00PM, how do I get the total work time between the two dates?

Thanks, Jon

Hey there.

I think you can get what you want by using an add-in. If you go to Tools
and then select Add-in, you will see another window in which you need to
check the box next to Analysis ToolPak. After this installs, you can go to
the formula function button and pick NetWorkDays when you are in the cell you
want to start the calculation. The formula wizard will help you input the
correct data. It should only consider business days, and then help you
eliminate holidays.

hope this helps

"Jon Ratzel" wrote:

I have two date columns formated like this:

1/22/08 2:19 PM

I need to subract them to get the total time but I need to exclude weekends
and hours not in the normal working day. If the start of the day is 8:30AM
and ends at 5:00PM, how do I get the total work time between the two dates?

Thanks, Jon

I think that'll work to exlude the weekend properly but there's also the
issue of non-working hours during the week. For instance if my time is 4:59PM
on a Tues and the next time is 8:30AM on Wed I would want it to calculate a
difference of 1 minutes or 00:01:00 (HH:MM:SS format.) Or if my first time is
4:59PM on a Tues and 8:30AM on the next Thur I'd like it to calculate
08:31:00. Does that make sense?

Thanks again for the help, Jon


"Red" wrote:

Hey there.

I think you can get what you want by using an add-in. If you go to Tools
and then select Add-in, you will see another window in which you need to
check the box next to Analysis ToolPak. After this installs, you can go to
the formula function button and pick NetWorkDays when you are in the cell you
want to start the calculation. The formula wizard will help you input the
correct data. It should only consider business days, and then help you
eliminate holidays.

hope this helps

"Jon Ratzel" wrote:

I have two date columns formated like this:

1/22/08 2:19 PM

I need to subract them to get the total time but I need to exclude weekends
and hours not in the normal working day. If the start of the day is 8:30AM
and ends at 5:00PM, how do I get the total work time between the two dates?

Thanks, Jon