Dates and Filters

[mattc1989]

Hello, so here is what I need. A bit of a challenge it seems.

I have a excel sheet that includes start dates and end dates. I am trying to create code or macro to have a box pop up and ask you to enter a date, i then want the sheet to filter the items based on if that date is the start date or the end date, OR is in between the start date and end date.

HELP PLEASE !!!!

[mattc1989]

Thank you in advance!

[mancubus]

welcome to vbax.

assuming topleft cell of your table is A1 and dates are in column C (column 3)

Sub FilterBetweenDates()

    Dim strDate As String, FilterDate As Date
    Dim StartDate As Long, EndDate As Long
    
    strDate = Application.InputBox("Enter A Date in mm/dd/yyyy format", "DATE")
    If IsDate(strDate) Then
        FilterDate = DateValue(strDate)
    Else
        MsgBox "Please enter a valid date! Quitting..."
        Exit Sub
    End If
    
    With Worksheets("MySheet") ' change worksheet name to suit
        StartDate = .Range("K1").Value 'or StartDate = DateValue("09/01/2014")
        EndDate = .Range("K2").Value 'or EndDate = DateValue("12/31/2014")
        
        If FilterDate >= StartDate And FilterDate <= EndDate Then
            .Cells(1).AutoFilter Field:=3, Criteria1:=">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate
        End If
    End With

End Sub

[mattc1989]

After I enter the date i get a error "subscript out of range"

welcome to vbax.

assuming topleft cell of your table is A1 and dates are in column C (column 3)

Sub FilterBetweenDates()

    Dim strDate As String, FilterDate As Date
    Dim StartDate As Long, EndDate As Long
    
    strDate = Application.InputBox("Enter A Date in mm/dd/yyyy format", "DATE")
    If IsDate(strDate) Then
        FilterDate = DateValue(strDate)
    Else
        MsgBox "Please enter a valid date! Quitting..."
        Exit Sub
    End If
    
    With Worksheets("MySheet") ' change worksheet name to suit
        StartDate = .Range("K1").Value 'or StartDate = DateValue("09/01/2014")
        EndDate = .Range("K2").Value 'or EndDate = DateValue("12/31/2014")
        
        If FilterDate >= StartDate And FilterDate <= EndDate Then
            .Cells(1).AutoFilter Field:=3, Criteria1:=">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate
        End If
    End With

End Sub

[mattc1989]

I have two date rows that I want to compare with N & O

[mancubus]

can you post your workbook (replace sensitive data with fake data)?

go advanced
manage attachments

[snb]

Why don't you use a table or autofilter.

[mattc1989]

I am trying to make this as easy as possible for the end user.

Why don't you use a table or autofilter.

[mattc1989]

attached below

[mancubus]

this is different. you have separate columns for Start end End dates. and values in these columns are strings not dates.

i will work on it.

[mancubus]

since you just want to filter the rows, i used a helper column, Column Y, to apply a filter.

assumption: values in Column N and O are always in this format: DDD MM/DD/YYYY HH:MM AM/PM

Sub FilterBetweenDates()
     
    Dim strDate As Date, FilterDate As Date
    Dim StartDate As Long, EndDate As Long, i As Long
     
    strDate = Application.InputBox("Enter A Date in mm/dd/yyyy format", "DATE")
    If IsDate(strDate) Then
        FilterDate = DateValue(strDate)
    Else
        MsgBox "Please enter a valid date! Quitting..."
        Exit Sub
    End If
    
    With Worksheets("Sheet1")
        .AutoFilterMode = False
        .Range("Y1") = "BetweenStartEnd"
        For i = 2 To .Cells.Find("*", , , , xlByRows, xlPrevious).Row
            StartDate = DateValue(Mid(.Range("N" & i), 5, 10))
            EndDate = DateValue(Mid(.Range("O" & i), 5, 10))
            If FilterDate >= StartDate And FilterDate <= EndDate Then
                .Range("Y" & i) = "Between"
            End If
        Next i
        .Cells(1).AutoFilter Field:=25, Criteria1:="=Between"
    End With

End Sub

[snb]

In the attachment:

how I converted text to dates (one liner macro).

The conversion of the data into a table. (Excel >2003)

Now the user can select any criterion to filter on.

[mattc1989]

Hello, i entered 10/01/2014 and i received a VB error "Run-time error "13": Type mismatch"

How can i fix that?

** This is highlighted ** See below

EndDate = DateValue(Mid(.Range("O" & i), 5, 10))

since you just want to filter the rows, i used a helper column, Column Y, to apply a filter.

assumption: values in Column N and O are always in this format: DDD MM/DD/YYYY HH:MM AM/PM

Sub FilterBetweenDates()
     
    Dim strDate As Date, FilterDate As Date
    Dim StartDate As Long, EndDate As Long, i As Long
     
    strDate = Application.InputBox("Enter A Date in mm/dd/yyyy format", "DATE")
    If IsDate(strDate) Then
        FilterDate = DateValue(strDate)
    Else
        MsgBox "Please enter a valid date! Quitting..."
        Exit Sub
    End If
    
    With Worksheets("Sheet1")
        .AutoFilterMode = False
        .Range("Y1") = "BetweenStartEnd"
        For i = 2 To .Cells.Find("*", , , , xlByRows, xlPrevious).Row
            StartDate = DateValue(Mid(.Range("N" & i), 5, 10))
            EndDate = DateValue(Mid(.Range("O" & i), 5, 10))
            If FilterDate >= StartDate And FilterDate <= EndDate Then
                .Range("Y" & i) = "Between"
            End If
        Next i
        .Cells(1).AutoFilter Field:=25, Criteria1:="=Between"
    End With

End Sub

[mancubus]

you dont need to quote previous messages.

perhaps have nonblank cells below your table.

change

 .Cells.Find("*", , , , xlByRows, xlPrevious).Row

to

 .Cells.(.Rows.Count, 1).End(xlUp).Row

or if cells in Column A are contiguous range

.Cells(1, 1).End(xlDown).Row

[mattc1989]

How should i enter the date ?? 10012014 or 10/01/2014 ??

[mattc1989]

it still highlights EndDate = DateValue(Mid(.Range("O" & i), 5, 10))

[mattc1989]

When i enter For i = 2 To .Cells.(.Rows.Count, 1).End(xlUp).Row it turns red and I cannot run the macro

[mattc1989]

Just to be clear on what I need....If I enter October 1, 2014 as the date I want any row that has a start date of Oct 1 or a end date of Oct 1 OR Oct 1 is between the start and end date.

Is that what you provided?

[mancubus]

what is international date format for your PC? mm/dd/yyyy or dd/mm/yyyy.

when dealing with dates in VBA with a format other than mm/dd/yyyy format, it's likely that you face problems.

DateSerial function is for handling the date format issues.

try below code to see which dates are returned for, say, Mon 09/01/2014 09:00 am, in column Y.
is it 09.01.2014 or 01.09.2014

Sub FilterBetweenDates2()
     
    Dim strDate As Date, FilterDate As Date
    Dim StartDate As Long, EndDate As Long, i As Long
     
    strDate = Application.InputBox("Enter A Date in mm/dd/yyyy format", "DATE")
    If IsDate(strDate) Then
        FilterDate = CDate(strDate)
    Else
        MsgBox "Please enter a valid date! Quitting..."
        Exit Sub
    End If
     
    With Worksheets("Sheet1")
        .AutoFilterMode = False
        .Range("Y1") = "Start"
        .Range("Z1") = "End"
        .Range("AA1") = "BetweenStartEnd"
        For i = 2 To .Cells.(.Rows.Count, 1).End(xlUp).Row
            .Range("Y" & i) = CDate(Mid(.Range("N" & i), 5, 10))
            .Range("Z" & i) = CDate(Mid(.Range("O" & i), 5, 10))
            If .Range("Y" & i) <= FilterDate And FilterDate <= .Range("Z" & i) Then
                .Range("AA" & i) = "Between"
            End If
        Next i
        .Cells(1).AutoFilter Field:=27, Criteria1:="=Between"
    End With
     
End Sub

[mattc1989]

Think its resolved, since one or two dates were blank, seems to be working thanks