CODEWITHSUNDEEP
excelvbadatetime
Joel Bastien
Joel Bastien

Reputation: 121

How to define a variable in between 2 date periods and time periods?

So I'm trying to define a variable or multiple variable so I can easily filter in my data set. Essentially I need to filter by date/time in my data set. The format of the date and time in the data set is as such: 2019-01-03 8:45:30 PM

What I want to do is to define a variable in order to always filter the data based on the current date. My data always changes every day, therefore my point of reference is today's date. Essentially I need to filter data starting from 2 days ago at 11am all the way to yesterdays date ending at 10am. How would i go about defining my variable? I tried doing the following: 

Dim StandardH As Date
StandardH = Date - 1 + (1 / 24)

And do the same thing all the way to 10 am and do - (1/24) all the way till 11am but that doesn't seem to work.

I realize that there's probably a simpler way of doing this. Any ideas?

Thanks,

Upvotes: 0

Views: 90

Answers (1)

chillin
chillin

Reputation: 4486

In VBA:

  • "2 days ago at 11 AM" translates to Date - 2 + TimeSerial(11, 0, 0)
  • "Yesterday at 10 AM" translates Date - 1 + TimeSerial(10, 0, 0)

That's in relation to the date and time the code is being run, and assumes by "2 days ago" you mean "the day prior to yesterday".

If I had some dates on "Sheet4" in the range "B7:B17" (assume there is a header in cell B6), then I think I could use the code below to filter from (and including) 2 days ago at 11 AM, up to (and including) yesterday at 10 AM:

Option Explicit

Sub FilterDatesInclusively()

    With ThisWorkbook.Worksheets("Sheet4")
        .AutoFilterMode = False

        Dim fromDate As Date
        fromDate = Date - 2 + TimeSerial(11, 0, 0)

        Dim toDate As Date
        toDate = Date - 1 + TimeSerial(10, 0, 0)

        .Range("B6:B17").AutoFilter Field:=1, Criteria1:=">=" & CDbl(fromDate), Operator:=xlAnd, Criteria2:="<=" & CDbl(toDate)
    End With

End Sub

Maybe there is some way of filtering without converting to double, but I couldn't work it out.

Upvotes: 1

Related Questions