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

 

[amaglam]

(

I even tried this

                    dblStartTime = CDate(Format(TimeSerial(intStartHour, 0, 0), "h:mm:ss"))
                    dblEndTime = CDate(Format(TimeSerial(intEndHour, 0, 0), "h:mm:ss"))

 

[MARK858]

As I stated in a previous post I think you might have your criteria the wrong way around.

  .AutoFilter Field:=7, Criteria1:=">" & dtStartTime, Operator:=xlAnd, Criteria2:="<=" & dtEndTime

 

[amaglam]

As I stated in a previous post I think you might have your criteria the wrong way around.

  .AutoFilter Field:=7, Criteria1:=">" & dtStartTime, Operator:=xlAnd, Criteria2:="<=" & dtEndTime

You are absolutely correct. I didnt know that the start time needed to come first. Thank you!

.AutoFilter Field:=1, Criteria1:="<" & dblStartTime, Operator:=xlAnd, Criteria2:="<" & dblEndTime

 

[MARK858]

You're welcome but where you have written

Criteria1:="<" & dblStartTime

should be

Criteria1:=">" & dblStartTime

As you want the times between the start and the finish

 

[amaglam]

Wait lol. Alomst everthing, it took care of the hours 0-6 but it has held onto data after my end time. Start time is 7 and end time is 18

 

[amaglam]

I want to hold onto the everything that is between 7 and 18, so my operators are set incorrectly?

 

[amaglam]

This did the job, not sure how its different than my original code

                    .AutoFilter Field:=1, Criteria1:="<=" & dblStartTime
                    .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
                    shData.AutoFilterMode = False
                    
                    .AutoFilter Field:=1, Criteria1:=">=" & dblEndTime
                    .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
                    shData.AutoFilterMode = False

 

[MARK858]

So you aren't filtering for the between dates, you are filtering for everything that isn't between the dates, then deleting the dates that aren't between the dates, leaving only the dates that are between the dates.

That would have been

 .AutoFilter Field:=7, Criteria1:="<" & dtStartTime, Operator:=xlOr, Criteria2:=">" & dtEndTime

 

[amaglam]

So you aren't filtering for the between dates, you are filtering for everything that isn't between the dates, then deleting the dates that aren't between the dates, leaving only the dates that are between the dates.

That would have been

 .AutoFilter Field:=7, Criteria1:="<" & dtStartTime, Operator:=xlOr, Criteria2:=">" & dtEndTime

Ah man, smh! Thank you, Sir.

 

[MARK858]

You're welcome