removing weekends
 

I am only wanting to count the hours between two dates that fall on Monday
thru Friday. In other words, I want to always exclude counting hours on
Weekends

The data is in the format of
6/27/06 12:10 - say that this is in cell A1
7/20/06 09:34 - say that this is in cell B1

=B1-A1
How do I do this ?
Thx for your assistance, it is much appreciated
- Dean

removing weekends
 

a few answers may mean this is simple do any activities start or finish
on a saturday or sunday? if the answer is no
and the answer to activites lasting more than a week is also no

then

=IF(WEEKDAY(a1,2)-WEEKDAY(b1,2)0,b1-a1-2,b1-a1)

if a week can be exceded but still no finishes or starts on sat or sun

=IF(WEEKDAY(A1,2)-WEEKDAY(B1,2)0,B1-A1-2*(1+TRUNC((B1-A1)/7)),B1-A1-2*TRUNC((B1-A1)/7))

regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=563847

removing weekends
 

Dean --

Don't know if you meant work hours or clock hours. Here's a formula for
work hours:

A
1 Start date/time
2 End date/time
3
4 =NETWORKDAYS(A1,A2)*8+(HOUR(A2)-HOUR(A1))

You can set it up to ignore holidays as well; it's got good documentation in
'help'.

HTH

"Dean" wrote:

I am only wanting to count the hours between two dates that fall on Monday
thru Friday. In other words, I want to always exclude counting hours on
Weekends

The data is in the format of
6/27/06 12:10 - say that this is in cell A1
7/20/06 09:34 - say that this is in cell B1

=B1-A1
How do I do this ?
Thx for your assistance, it is much appreciated
- Dean

removing weekends
 

pdberger,
Thx for replying -
If you mean Workhours is only considering (8), that could be the situation
needed, my boss hasn't decided if he wants to base the difference on (8) hrs
per day or as whole days of (24) hours. So, I guess it would be good to know
ways to do both. Dean

"pdberger" wrote:

Dean --

Don't know if you meant work hours or clock hours. Here's a formula for
work hours:

A
1 Start date/time
2 End date/time
3
4 =NETWORKDAYS(A1,A2)*8+(HOUR(A2)-HOUR(A1))

You can set it up to ignore holidays as well; it's got good documentation in
'help'.

HTH

"Dean" wrote:

I am only wanting to count the hours between two dates that fall on Monday
thru Friday. In other words, I want to always exclude counting hours on
Weekends

The data is in the format of
6/27/06 12:10 - say that this is in cell A1
7/20/06 09:34 - say that this is in cell B1

=B1-A1
How do I do this ?
Thx for your assistance, it is much appreciated
- Dean

removing weekends
 

Dean --

Wasn't sure from your response if you saw just to change the 8 to 24
depending on what your boss wants.

"Dean" wrote:

pdberger,
Thx for replying -
If you mean Workhours is only considering (8), that could be the situation
needed, my boss hasn't decided if he wants to base the difference on (8) hrs
per day or as whole days of (24) hours. So, I guess it would be good to know
ways to do both. Dean

"pdberger" wrote:

Dean --

Don't know if you meant work hours or clock hours. Here's a formula for
work hours:

A
1 Start date/time
2 End date/time
3
4 =NETWORKDAYS(A1,A2)*8+(HOUR(A2)-HOUR(A1))

You can set it up to ignore holidays as well; it's got good documentation in
'help'.

HTH

"Dean" wrote:

I am only wanting to count the hours between two dates that fall on Monday
thru Friday. In other words, I want to always exclude counting hours on
Weekends

The data is in the format of
6/27/06 12:10 - say that this is in cell A1
7/20/06 09:34 - say that this is in cell B1

=B1-A1
How do I do this ?
Thx for your assistance, it is much appreciated
- Dean

removing weekends
 

That's great, what a help. Thx again. Dean

"pdberger" wrote:

Dean --

Wasn't sure from your response if you saw just to change the 8 to 24
depending on what your boss wants.

"Dean" wrote:

pdberger,
Thx for replying -
If you mean Workhours is only considering (8), that could be the situation
needed, my boss hasn't decided if he wants to base the difference on (8) hrs
per day or as whole days of (24) hours. So, I guess it would be good to know
ways to do both. Dean

"pdberger" wrote:

Dean --

Don't know if you meant work hours or clock hours. Here's a formula for
work hours:

A
1 Start date/time
2 End date/time
3
4 =NETWORKDAYS(A1,A2)*8+(HOUR(A2)-HOUR(A1))

You can set it up to ignore holidays as well; it's got good documentation in
'help'.

HTH

"Dean" wrote:

I am only wanting to count the hours between two dates that fall on Monday
thru Friday. In other words, I want to always exclude counting hours on
Weekends

The data is in the format of
6/27/06 12:10 - say that this is in cell A1
7/20/06 09:34 - say that this is in cell B1

=B1-A1
How do I do this ?
Thx for your assistance, it is much appreciated
- Dean

removing weekends
 

Dav,
I'll give your info a try, I think I got what I needed from pdberger.
Thanks for your reply. Dean

"Dav" wrote:


a few answers may mean this is simple do any activities start or finish
on a saturday or sunday? if the answer is no
and the answer to activites lasting more than a week is also no

then

=IF(WEEKDAY(a1,2)-WEEKDAY(b1,2)0,b1-a1-2,b1-a1)

if a week can be exceded but still no finishes or starts on sat or sun

=IF(WEEKDAY(A1,2)-WEEKDAY(B1,2)0,B1-A1-2*(1+TRUNC((B1-A1)/7)),B1-A1-2*TRUNC((B1-A1)/7))

regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=563847