Dealing with Time Values

[amaglam]

Hi, would someone please help me understand why my column "G" that has a custom value set to "h:mm:ss" has a VarType of double? I am trying to filter for rows that fall between my start hour and end hour, but I cant seem to get the types to match. Looking at a cell value in column "G", it shows "23:00:00", and upon clicking into the cell it shows "11:00:00 PM". I've tried man different variations with TimeSerial as but can't seem to get them to match.

                    dtStartTime = Format(TimeSerial(intStartHour, 0, 0), "h:mm:ss")
                    dtEndTime = Format(TimeSerial(intEndHour, 0, 0), "h:mm:ss")
                  
                    .AutoFilter Field:=7, Criteria1:=">" & dtEndTime, Operator:=xlAnd, Criteria2:="<=" & dtStartTime
                    .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
                    shData.AutoFilterMode = False

 

[Akuini]

These 2 methods works for me:

Sub try1()
Dim x As Double, y As Double
x = Range("C1")
y = Range("D1")
Range("A1").AutoFilter Field:=1, Criteria1:=">" & x, Operator:=xlAnd, Criteria2:="<" & y

End Sub


Sub try2()
Dim x As Double, y As Double
x = TimeValue("13:30:11")
y = TimeValue("17:00:01")
Range("A1").AutoFilter Field:=1, Criteria1:=">" & x, Operator:=xlAnd, Criteria2:="<" & y

End Sub

Book1
	A	B	C	D
1	data		13:30:11	17:00:01
2	11:00:00
3	12:00:00
4	13:00:00
5	14:00:00
6	15:00:00
7	16:00:00
8	17:00:00
9	18:00:00
Sheet2

both have the same result:

Book1
	A	B	C	D
1	data		13:30:11	17:00:01
5	14:00:00
6	15:00:00
7	16:00:00
8	17:00:00
10
Sheet2

 

[amaglam]

:/ still doesnt like it

the data in column "G" show 0:00:05 but when i click into it its 12:00:05 AM

 

[Akuini]

1. Do you mean the cell shows 0:00:05 and in formula bar shows 12:00:05 AM? or is it the other way around?
2. Can you post a sample data?
3. How does intStartHour get its value?

 

[Akuini]

I change the time format to "hh:mm:ss AM/PM", and both codes still works:

Book1
	A	B	C	D
1	data		01:30:11 PM	05:00:01 PM
2	11:00:00 AM
3	12:00:00 PM
4	01:00:00 PM
5	02:00:00 PM
6	03:00:00 PM
7	04:00:00 PM
8	05:00:00 PM
9	06:00:00 PM
10
Sheet2

Book1
	A	B	C	D
1	data		01:30:11 PM	05:00:01 PM
5	02:00:00 PM
6	03:00:00 PM
7	04:00:00 PM
8	05:00:00 PM
10
Sheet2

 

[amaglam]

The cell value is 0:00:05. I set intStartHour as an Integer

 

[MARK858]

A time value is always a decimal so you definitely don't want it as an Integer. You also don't really want to be using Format as it is a string function.

 

[amaglam]

The reason I used intStartHour was because I do a check to see if its between the start time and end time, and I though I would be able to use TimeSerial to change both column G and my intStartHour. So are you guys saying that I should declare a new variable and reference intStartHour?

 

[MARK858]

The way you are using intStartHour (just as a digit within TIMESERIAL) is fine as an Integer, if you were using it as an actual time then it wouldn't be.
Looking at your original code are you sure you have the criteria the right way around? If you want to filter the times between surely you want it > the start time and < the end time?

 

[amaglam]

Is it not valid to use .NumberFormat = "h:mm:ss" on Column G and set my variables like I had them?

 dtStartTime = Format(TimeSerial(intStartHour, 0, 0), "h:mm:ss")
dtEndTime = Format(TimeSerial(intEndHour, 0, 0), "h:mm:ss")