Filter spreadsheet by exact date (MM/DD/YYYY)

Question

I am building a tool that collects data from a userform and then the end user is supposed to be able to filter the master set of all the data and copy it into an email. For some reason my filter does not refine the set down to the exact day, but rather it only goes down to the month. The date is stored in a userform textbox that autopopulates with the current date, but the user is able to change it if they choose to report on older data. Here is my code for the filter;

    Private Sub CommandButton6_Click()


Sheets("Master").ListObjects("Append1").Range.AutoFilter Field:=1, Operator:= _
        xlFilterValues, Criteria2:=Array(1, date2_txtb)

End Sub

date2_txtb = Textbox containing date Append1: Amended query making up the "Master Set"

UPDATE:

Private Sub UserForm_Initialize()
date2_txtb.Text = Format(Now(), "MM/DD/YYYY")
End Sub

Each time I see a question about time or date in Excel I feel compelled to ask the same question: are these actual dates or do they just look like dates? You might want to read through this to better understand dates / times in Excel: http://stackoverflow.com/a/37101358/1153513 This is probably also a good read: http://stackoverflow.com/a/38001028/1153513 Afterwards, please update your question and post and share with us whether you verified if these are **all** actual dates. Also, please include a check into your code to ensure that `date2_txtb` is an acutal date. — Ralph, Nov 06 '17 at 17:52
Thank you for posting those links, I was actually really curious about how excel formatted dates as numbers and visa-versa. I updated my question which will hopefully shed some light on this. I do have date2_txtb formatted as a date (I think), but what would you suggest in order to be able to successfully filter all of my data? — accortdr, Nov 06 '17 at 18:02

score 2 · Accepted Answer · answered Nov 06 '17 at 18:19

I think this is what you want.

date2_txtb = "11/11/2017"

Dim vDate As Date

Dim lDate As Long

vDate = DateSerial(Year(date2_txtb), Month(date2_txtb), Day(date2_txtb))

lDate = vDate

ThisWorkbook.Sheets("Master").ListObjects("Append1").Range.AutoFilter Field:=1, Criteria1:=">=" & lDate, Operator:=xlAnd, Criteria2:="<" & lDate + 1

Filter spreadsheet by exact date (MM/DD/YYYY)

1 Answers1