removing weekends

[Dean]

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

[Dav]

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

[pdberger]

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
>

[Dean]

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
> >

[pdberger]

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
> > >

[Dean]

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
> > > >

[Dean]

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
>
>