Remove weekends

[rhhince]

I have 2 dates and time:
5/21/2010 11:34 is in A2
5/25/2010 11:34 is in A3

If I subtract them, I get 4 days in cell A4.
I would like to subtract the weekends and give me a total of 2 days.

I came across this before, but forgot how to do it. Any help! Thanks.

 

[Lars-Åke Aspelin]

I have 2 dates and time:
5/21/2010 11:34 is in A2
5/25/2010 11:34 is in A3

If I subtract them, I get 4 days in cell A4.
I would like to subtract the weekends and give me a total of 2 days.

I came across this before, but forgot how to do it. Any help! Thanks.

Assuming that the hours and minutes are significant and not always the
same in A2 and A3.
Also assuming that neither A2 or A3 hold a time on a weekend.

Try this formula:

=A3-A2-SUMPRODUCT((A2+ROW(1:999)<A3)*(WEEKDAY(A2+ROW(1:999),2)>5))

The 999 is a number bigger than the largest expected difference
between A3 and A2.

Hope this helps / Lars-Åke

 

[Ashish Mathur]

Hi,

Try this

=networkdays(A2,A3). With the dates mentioned below, the answer would be 3

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com