Filter on TODAY - a year

  • Thread starter Thread starter Godzilla
  • Start date Start date
G

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
 
Hi,
You need to be more specific and post your whole formula.
today()-365=37463 What does that mean? Row 37463?
 
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
 
Hi,
You need to be more specific and post your whole formula.
today()-365=37463 What does that mean? Row 37463?
Click to expand...
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
 
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?
Click to expand...

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