VBA Excel 2007 - Filtering date time problems.

[BeZno]

Hello everyone,

I have a data set that keeps track of temperature every 10 minutes. Now I want to make a button where the user can enter
a day with time to show the data. Before I try to make it work with buttons etc. I want to get the filtering done first.

This is what I've got now:

Sub FilterByDateTime()

Dim dDate As Date
Dim dbDate As Double
Dim dbDate2 As Double


If (IsDate(Range("H1"))) And (IsDate(Range("I1"))) Then


    dbDate = Range("H1")
    dbDate2 = Range("I1")
    dbDate = DateSerial(Year(dbDate), Month(dbDate), Day(dbDate)) + _
        TimeSerial(Hour(dbDate), Minute(dbDate), Second(dbDate))
    
    dbDate2 = DateSerial(Year(dbDate2), Month(dbDate2), Day(dbDate2)) + _
        TimeSerial(Hour(dbDate2), Minute(dbDate2), Second(dbDate2))
    
    Range("A1").AutoFilter
    Range("A1").AutoFilter Field:=6, Criteria1:=">" & dbDate, _
            Operator:=xlAnd, Criteria2:="<=" & dbDate2
End If


End Sub

This works perfectly for days. As they are seen as round numbers within Excel.
But I got data every 10 minutes, so I would like to filter it showing from 15:00 till 19:00 for example.
Here is where the problem begins. Excel shows date including time as a decimal number. When this number is put back in to the filter
and when I check it by the advanced filter screen, I see a laaaarge round number.

This is the date I want to get in dd//mm//yyyy hh:mm:ss format 05-07-2016 01:00:00 or in Excel number 42556,0416666667.
What I see within the filter is 425560416666667 and so on. The , is removed and that's why I don't see any data at all.

Is there a way to fix this?

Thanks in advanced!

 

[BeZno]

I've used a workarround for this problem.

In Column A I have the number, 1,2, ..., 4000 etc. So every date has it's own number.
The Data is given for every 10 minutes, every day has 144 datapoints.
Let's say I want to see the data on 10-5-2016 only. (My Data starts at 4-5-2016, Excel number 42494)

Using this I can find what normal number is 10-5-2016, then I use that for the filter.
42500 - 42494 = 6
6 *144 = 846

The same I do twice, so the user can put in a range.