CODEWITHSUNDEEP
vbaexcel
Robillard
Robillard

Reputation: 117

Filter by month and if a date was a year ago?

I am trying to make a report that pulls up monthly birthdays for people who have been at the company for longer than one year. I am having an issue on figuring out how to do the logic to do what I want. What I am doing is having the user select a month they want to search for, then it will filter out that month then select people who have been at the company longer than a year according to the month not the exact day. So if they would be here a year in two weeks but it is still in October for example, then I want to consider them being here a year to give them a birthday gift.

I want to filter my list so that I only have a list of people who have a birthday in the month I choose, and who have been here for at least a year. But here is the issue, For example October. Right now I am using my current date to decide if it has been a year. But I only want to run this once a month. So if they have a birthday in October AND if they were hired in October LAST YEAR then I want them on my list as well even if it technically hasn't been a year. So if it would be a year according to what month they were hired, then I want to consider them a year and have them included.

I tried to explain it the best I could I am going in circles I think from thinking about this. Here is some code that I have been working on but it does not do what I want. It is close but this goes to the EXACT date of what day you run the report on. I need it so if you have been here a year IN THAT MONTH, then you can stay on the list. 

With wb.Sheets(2)
    For lngRow = lngRows To 2 Step -1
        If Date - Range("F" & lngRow) < 365 Then
            wb.Sheets(2).Rows(lngRow).EntireRow.Delete
        End If
    Next
End With

enter image description here

The first table is raw data with people who have birthdays in October. The second table is who should be left if for example the actual date is October 15th or any date in October. Everyone in October should still get a birthday card if they have been here a year.

Any help is appreciated! My brain hurts...

Upvotes: 0

Views: 92

Answers (2)

tigeravatar
tigeravatar

Reputation: 26640

Using your provided sample data, something like this should work for you:

Sub tgr()

    Dim ws As Worksheet
    Dim TargetDate As Date

    Set ws = ActiveWorkbook.Sheets(2)
    TargetDate = DateSerial(Year(Now()) - 1, Month(Now()) + 1, 1)

    With ws.Range("A1:D" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
        .AutoFilter
        .AutoFilter 2, "<" & TargetDate
        .AutoFilter 3, "=" & Month(Now())
    End With

End Sub

Upvotes: 1

pnuts
pnuts

Reputation: 59440

You might want to consider putting a date, say 10/28/17, in E1 and in E2 and copied down to suit:

=AND(B2<EOMONTH(E$1,-13),C2=MONTH(E$1))

Then you should be able to filter on ColumnE to select TRUE without further bother, other than changing E1 to suit.

Upvotes: 0

Related Questions