help with dates in the past

[Bradly]

Hi.

I currently have a code that correctly filters the case reviews due for a
given month (for my example, it is for March). I want, however, to make a
file that filters all of the past due case reviews. Here is my current code:

Sub GetOverdueReviews()
'
' GetOverdueReviews Macro
'
'

'
Sheets("F Cases").Activate
Application.Goto Reference:="R1C1"


Dim FilterRange As Range
Dim CopyRange As Range
Set FilterRange = Range("L1:L5000") 'Header in row
Set CopyRange = Range("A1:M5000")
FilterRange.AutoFilter Field:=1, Criteria1:="03 10"
CopyRange.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("Overdue Reviews").Range("A3")
Application.CutCopyMode = False
Sheets("F Cases").Activate
Selection.AutoFilter
Application.Goto Reference:="R1C1"
Sheets("Overdue Reviews").Activate
Application.Goto Reference:="R1C1"
End Sub

How can this be modified to get all of the past due case reviews (they could
go back as far as 10/2008)? The date format used in my code is "03 10".
Thanks.

 

[OssieMac]

Hi Bradley,

Am I correct in assuming that you want to include all data prior to and
including a specific month?

If the dates in the filtered column are actual dates and formatted to mm yy
then you may only have to change the filter criteria to the following.

FilterRange.AutoFilter Field:=1, Criteria1:="<=Mar-2010"

Excel VBA has problems with numerics in dates when the date to be entered is
a string like used with AutoFilter code. However, I have always been
successful by using the alpha literal month. (For interest in the above,
Excel actually evaluates Mar-2010 as 1- Mar-2010.)

If the above does not work and your regional date format is m/d/y and the
filtered column contains actual dates and formatted to mm yy then try the
following.

FilterRange.AutoFilter Field:=1, Criteria1:="<=Mar-31-2010"

If the above does not work then get back to me with some answers to the
following questions.

What version of xl are you using?

Are the values in the filtered column actual dates and formatted as mm yy or
are they simply text? (I can tell you how to change from text to date format
and display as mm yy but I need an answer to the following question first.)

What is your regional date format (d/m/y or m/d/y)?

 

[Bradly]

Hi. I tried the two FilterRange options you gave--the first one didn't do
anything and the second one listed the majority of the original list, which I
know is not correct. I tried changing the date format of the column, and it
showed it originally listed as "general"--I am thinking the dates that I
import (mm yy) are probably text. The dates didn't change when I changed the
column date format.

I am using Excel 2003 and the regional date settings are m/d/y. Let me know
if there is anything else you need to help. Thanks.

 

[OssieMac]

Hi again Bradley,

Insert a column beside the date column and format it to date.

I assume you have column headers and the first row of data is in row 2.

In the following formula I have used N2 for the text data. Replace N with
whatever column your current text dates are in.

Insert the following formula in the new column and copy it down.

=DATEVALUE(LEFT(N2,2)&"/"&"01/"&RIGHT(N2,2))

The formula should return a date as the 1st day of month.

Select the column and format it to mm/yy (or mmm yy if you want the alpha
abbreviation of the month.)

You can record you code to set the Filter criteria but edit the criteria in
the code as per my previous post. You might have to use custom criteria when
setting and set it to less than apr 1 2010 because mar 2010 translates to mar
1 2010 in the code.)

 

[OssieMac]

I forgot to tell you that after copying the formula down the column to select
the new column and Copy -> Paste Special -> Values over top of itself to
remove the formulas and then you can delete the old column of dates so that
your worksheet appears the same as before.

 

[Bradly]

This works great. Thanks.