Advanced Filter Using Calculaions
 

OK... So I thought this was working when I did it yesterday... but
something isn't...

I have columns A8:A5000 as my range. Column D has the dates I want to
filter. D1 is BeforeYesterday (not one of my data headers) and D2 is
"=D9<(today()-1)".

I have no idea what it's filtering out but it's not the dates I'm looking
for... for example... I changed one of the dates to a date in 2007 and it
was in the filtered data... I checked the column to make sure it was data
type date...

Thoughts?

Thanks much!
Tara

"Ron Coderre" wrote:

Try this:

With a list of dates in A4:A100
A4: MyDates

A1: MyCriteria (or blank or any other text that is not a column title)
A2: =A5<(TODAY()-1)


From the Excel main menu:
<data<filter<advanced filter
List Range: $A$4:$A$100
Criteria: $A$1:$A$2
Click the [OK] button

Using that technique the Advanced Filter will sequentially apply the formula
in A2 to each data cell in the list range, beginning with A5.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"T_Amee" wrote:

Good Morning,

I have a file that has a column of dates. I would like to use advanced
filter to filter anything with a date that is <today()-1 but I can't seem to
make the fomula work. I can make it filter on =today()-1 but when I use the
< it doesn't calculate today's date and gives me nothing.

I'm sure it's simple but I'm missing something.
Thanks!
Tara

Advanced Filter Using Calculaions
 

I built a model of your structure (including dates from prior and future
years) and had no problem displaying only dates prior to yesterday. Verify
your settings.....Here's what I did:

D1: BeforeYesterday
D2: =D9<(TODAY()-1)

From the Excel main menu:
<data<filter<advanced filter

ChecK Filter the list , in-place
Uncheck: Unique records only
List Range: $A$8:$S$5000
Criteria Range: $D$1:$D$2
Click the [OK] button

Also verify that all Col_D items are dates

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"T_Amee" wrote:

OK... So I thought this was working when I did it yesterday... but
something isn't...

I have columns A8:A5000 as my range. Column D has the dates I want to
filter. D1 is BeforeYesterday (not one of my data headers) and D2 is
"=D9<(today()-1)".

I have no idea what it's filtering out but it's not the dates I'm looking
for... for example... I changed one of the dates to a date in 2007 and it
was in the filtered data... I checked the column to make sure it was data
type date...

Thoughts?

Thanks much!
Tara

"Ron Coderre" wrote:

Try this:

With a list of dates in A4:A100
A4: MyDates

A1: MyCriteria (or blank or any other text that is not a column title)
A2: =A5<(TODAY()-1)


From the Excel main menu:
<data<filter<advanced filter
List Range: $A$4:$A$100
Criteria: $A$1:$A$2
Click the [OK] button

Using that technique the Advanced Filter will sequentially apply the formula
in A2 to each data cell in the list range, beginning with A5.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"T_Amee" wrote:

Good Morning,

I have a file that has a column of dates. I would like to use advanced
filter to filter anything with a date that is <today()-1 but I can't seem to
make the fomula work. I can make it filter on =today()-1 but when I use the
< it doesn't calculate today's date and gives me nothing.

I'm sure it's simple but I'm missing something.
Thanks!
Tara

Advanced Filter Using Calculaions
 

Bless you... it was a date format thing... I did a copy/paste special as
values and reformatted as dates... now it's working great...

Thanks so much!!!!
Have a good day...
Tara



"Ron Coderre" wrote:

I built a model of your structure (including dates from prior and future
years) and had no problem displaying only dates prior to yesterday. Verify
your settings.....Here's what I did:

D1: BeforeYesterday
D2: =D9<(TODAY()-1)

From the Excel main menu:
<data<filter<advanced filter

ChecK Filter the list , in-place
Uncheck: Unique records only
List Range: $A$8:$S$5000
Criteria Range: $D$1:$D$2
Click the [OK] button

Also verify that all Col_D items are dates

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"T_Amee" wrote:

OK... So I thought this was working when I did it yesterday... but
something isn't...

I have columns A8:A5000 as my range. Column D has the dates I want to
filter. D1 is BeforeYesterday (not one of my data headers) and D2 is
"=D9<(today()-1)".

I have no idea what it's filtering out but it's not the dates I'm looking
for... for example... I changed one of the dates to a date in 2007 and it
was in the filtered data... I checked the column to make sure it was data
type date...

Thoughts?

Thanks much!
Tara

"Ron Coderre" wrote:

Try this:

With a list of dates in A4:A100
A4: MyDates

A1: MyCriteria (or blank or any other text that is not a column title)
A2: =A5<(TODAY()-1)


From the Excel main menu:
<data<filter<advanced filter
List Range: $A$4:$A$100
Criteria: $A$1:$A$2
Click the [OK] button

Using that technique the Advanced Filter will sequentially apply the formula
in A2 to each data cell in the list range, beginning with A5.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"T_Amee" wrote:

Good Morning,

I have a file that has a column of dates. I would like to use advanced
filter to filter anything with a date that is <today()-1 but I can't seem to
make the fomula work. I can make it filter on =today()-1 but when I use the
< it doesn't calculate today's date and gives me nothing.

I'm sure it's simple but I'm missing something.
Thanks!
Tara

Advanced Filter Using Calculaions
 

Thanks for the feedback, Tara.....I appreciate knowing what the issue was and
how you fixed it.

***********
Regards,
Ron

XL2002, WinXP


"T_Amee" wrote:

Bless you... it was a date format thing... I did a copy/paste special as
values and reformatted as dates... now it's working great...

Thanks so much!!!!
Have a good day...
Tara



"Ron Coderre" wrote:

I built a model of your structure (including dates from prior and future
years) and had no problem displaying only dates prior to yesterday. Verify
your settings.....Here's what I did:

D1: BeforeYesterday
D2: =D9<(TODAY()-1)

From the Excel main menu:
<data<filter<advanced filter

ChecK Filter the list , in-place
Uncheck: Unique records only
List Range: $A$8:$S$5000
Criteria Range: $D$1:$D$2
Click the [OK] button

Also verify that all Col_D items are dates

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"T_Amee" wrote:

OK... So I thought this was working when I did it yesterday... but
something isn't...

I have columns A8:A5000 as my range. Column D has the dates I want to
filter. D1 is BeforeYesterday (not one of my data headers) and D2 is
"=D9<(today()-1)".

I have no idea what it's filtering out but it's not the dates I'm looking
for... for example... I changed one of the dates to a date in 2007 and it
was in the filtered data... I checked the column to make sure it was data
type date...

Thoughts?

Thanks much!
Tara

"Ron Coderre" wrote:

Try this:

With a list of dates in A4:A100
A4: MyDates

A1: MyCriteria (or blank or any other text that is not a column title)
A2: =A5<(TODAY()-1)


From the Excel main menu:
<data<filter<advanced filter
List Range: $A$4:$A$100
Criteria: $A$1:$A$2
Click the [OK] button

Using that technique the Advanced Filter will sequentially apply the formula
in A2 to each data cell in the list range, beginning with A5.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"T_Amee" wrote:

Good Morning,

I have a file that has a column of dates. I would like to use advanced
filter to filter anything with a date that is <today()-1 but I can't seem to
make the fomula work. I can make it filter on =today()-1 but when I use the
< it doesn't calculate today's date and gives me nothing.

I'm sure it's simple but I'm missing something.
Thanks!
Tara