Advanced Filter Using Calculaions

T

T_Amee

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
 
R

Ron Coderre

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
 
P

Peo Sjoblom

In your criteria range, remove the header you would normally use and leave
that blank, then in the cell below put

=A4<TODAY()-1

where A4 is the first cell in the date range (first data cell, not header)

then use the same criteria range including the blank header so if the
criteria range that didn't work had a header called Dates in lets say E1 and
a condition in E2 you still would use $E$1:$E$2 as criteria range albeit
with E1 empty of contents



always when you use a formula as criteria you need to leave the criteria
header blank and refer to the first data cell in the range you want to
filter
--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
 
R

Ron Coderre

Just a minor point, Peo...

With a formula criteria, the first cell can be anything except a column
title from the Data List.

Using your example, E1 could be "BeforeYesterday", which is more descriptive
than just a blank, while making it more obvious that E1 is part of the
criteria.

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

XL2002, WinXP
 
T

T_Amee

I KNEW it had to be something simple... LOL those are always the worst to
find... :) Thank you both so much... it's working like a charm now... I
was even able to do it on two seperate columns as an OR... which is a
bonus...

Cheers
Tara
 
T

T_Amee

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 said:
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 said:
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
Click to expand...
Click to expand...
 
T

T_Amee

Sorry... saw the typo as I clicked post...
My data range is $a$8:$s:5000.

Ron Coderre said:
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 said:
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
Click to expand...
Click to expand...
 
R

Ron Coderre

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
T

T_Amee

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


:

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
Click to expand...
Click to expand...
Click to expand...
 
R

Ron Coderre

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

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

XL2002, WinXP


T_Amee said:
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 said:
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 said:
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

:

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


:

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
Click to expand...
Click to expand...
Click to expand...
 
Top