Prev: run external programs
Next: IF ERROR FORMULA
From: Roady on 24 May 2010 11:35
Hi!

Columns A and B both have dates entered. Col A is a start date and Col B is
an end date.

I want the user to filter using a combination of the two. For example...
I want to show all lines of data that have dates that fall between 1/1/10
and 5/5/10. That could mean that the start date or the end date falls within
this range. So even if Col A starts at 12/20/09, but in Col B, it is ends on
1/15/10, it should still show this line of data.

can you help with a formula or filter to achieve this combo? I think it
would be an "OR" statement of some kind possibly since it doesn't need to
meet both Col criteria, just 1 in order to be shown.

Thanks! R
From: Luke M on 24 May 2010 13:11
Can't do it with only the AutoFilter...

Lets say you put your start/stop dates in Y1 and Z1 respectively.
Formula:
=OR(AND(A2>=Y$1,A2<=Z$1),AND(B2>=Y$1,B2<=Z$1))
Coyp down as needed, then AutoFilter for TRUE on this column.

--
Best Regards,

Luke M
"Roady" <Roady(a)discussions.microsoft.com> wrote in message
news:4ED7E01C-38FE-4D11-B1FE-4AAA7E220D98(a)microsoft.com...
> Hi!
>
> Columns A and B both have dates entered. Col A is a start date and Col B
> is
> an end date.
>
> I want the user to filter using a combination of the two. For example...
> I want to show all lines of data that have dates that fall between 1/1/10
> and 5/5/10. That could mean that the start date or the end date falls
> within
> this range. So even if Col A starts at 12/20/09, but in Col B, it is ends
> on
> 1/15/10, it should still show this line of data.
>
> can you help with a formula or filter to achieve this combo? I think it
> would be an "OR" statement of some kind possibly since it doesn't need to
> meet both Col criteria, just 1 in order to be shown.
>
> Thanks! R


 | 
Pages: 1
Prev: run external programs
Next: IF ERROR FORMULA