Filter on TODAY - a year

[Godzilla]

I have attempted to use a custom filter like: >TODAY()-365
to obtain the records for the past year. This only jumps to a row beyond
the current number of active records.

If I use > DD/MM/YYYY (entering the actual day, month and year as
numerals), it works as expected.

However, I would like to write a macro which would produce the prior
year's records always beginning with the current date. Can this be done?

Thanks,

Godzilla

 

[jaf]

Hi,
You need to be more specific and post your whole formula.
today()-365=37463 What does that mean? Row 37463?

 

[Andy Brown]

Hi Godzilla (thought you were dead).

I really need to work on date/text stuff properly but ...

In F1, I entered this formula :

=">="&DATEVALUE(DAY(TODAY())&"/"&MONTH(TODAY())&"/"&YEAR(TODAY())-1)

This returns *the string* >37463.

Then with a bunch of dates in column A and A1 selected,

Selection.AutoFilter Field:=1, Criteria1:=Range("F1")

HTH,
Andy

 

[Godzilla]

Hi,
You need to be more specific and post your whole formula.
today()-365=37463 What does that mean? Row 37463?

To be more specific, what I have done manually is:

Data
Filter
Autofilter
Select Custom from drop the down menu
Show rows where ("Purchased" [column heading])
is greater than

That is the point where I can filter my database IF I enter the date
manually.

What I want to do is have the autofilter insert the current date MINUS
a year so that I can show all of the rows of inventory that were
purchased in the past year.

Then, I want to record all of the steps on a Macro (which I will assign
to a button) so that it can give me a filtered list of a year's
inventory - without my having to do all of the steps by hand.

Thanks,

Godzilla

 

[Debra Dalgleish]

To easily filter without a macro, you can add a column to your table,
and calculate if the date is within the past. For example, if the dates
are in column A:

1. Insert a column in the table.
2. Add a heading, e.g. ThisYr
3. In the first data row, enter a formula to calculate if the purchase
occurred within the past year. If the data starts in row 2:
=A2>=TODAY()-365
4. Copy this formula down to the last row of data.
5. To find the purchases for the current year, filter the ThisYr column
for TRUE

To use a macro, you have to ensure that the date is formatted to match
the dates on the worksheet:

Sub FilterDate()
Range("A2").AutoFilter Field:=1, _
Criteria1:=">=" & Format(Date - 365, "m/d/yy"), _
Operator:=xlAnd
End Sub

Hi,
You need to be more specific and post your whole formula.
today()-365=37463 What does that mean? Row 37463?

To be more specific, what I have done manually is:

Data
Filter
Autofilter
Select Custom from drop the down menu
Show rows where ("Purchased" [column heading])
is greater than

That is the point where I can filter my database IF I enter the date
manually.

What I want to do is have the autofilter insert the current date MINUS
a year so that I can show all of the rows of inventory that were
purchased in the past year.

Then, I want to record all of the steps on a Macro (which I will assign
to a button) so that it can give me a filtered list of a year's
inventory - without my having to do all of the steps by hand.

Thanks,

Godzilla

 

[Andy Brown]

OK, I checked the proper way to do the date thing from Harald Staff's FAQ --

=">"&DATE((YEAR(TODAY())-1),MONTH(TODAY()),DAY(TODAY()))

Although you could probably find a way to skip the helper cell (formula) and
write a rolling "one year ago" into the code.

Rgds,
Andy