Dealing with Time Values

amaglam

New Member
Joined
Dec 11, 2020
Messages
25
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
                    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

New Member
Joined
Dec 11, 2020
Messages
25
Office Version
  1. 2016
Platform
  1. Windows
I even tried this
VBA Code:
                    dblStartTime = CDate(Format(TimeSerial(intStartHour, 0, 0), "h:mm:ss"))
                    dblEndTime = CDate(Format(TimeSerial(intEndHour, 0, 0), "h:mm:ss"))
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,304
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
As I stated in a previous post I think you might have your criteria the wrong way around.

Rich (BB code):
  .AutoFilter Field:=7, Criteria1:=">" & dtStartTime, Operator:=xlAnd, Criteria2:="<=" & dtEndTime
 

amaglam

New Member
Joined
Dec 11, 2020
Messages
25
Office Version
  1. 2016
Platform
  1. Windows
As I stated in a previous post I think you might have your criteria the wrong way around.

Rich (BB code):
  .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!
VBA Code:
.AutoFilter Field:=1, Criteria1:="<" & dblStartTime, Operator:=xlAnd, Criteria2:="<" & dblEndTime
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,304
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You're welcome but where you have written
Rich (BB code):
Criteria1:="<" & dblStartTime
should be
Rich (BB code):
Criteria1:=">" & dblStartTime
As you want the times between the start and the finish
 

amaglam

New Member
Joined
Dec 11, 2020
Messages
25
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Dec 11, 2020
Messages
25
Office Version
  1. 2016
Platform
  1. Windows
I want to hold onto the everything that is between 7 and 18, so my operators are set incorrectly?
 

amaglam

New Member
Joined
Dec 11, 2020
Messages
25
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

This did the job, not sure how its different than my original code
VBA 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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,304
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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
Rich (BB code):
 .AutoFilter Field:=7, Criteria1:="<" & dtStartTime, Operator:=xlOr, Criteria2:=">" & dtEndTime
 
Last edited:

amaglam

New Member
Joined
Dec 11, 2020
Messages
25
Office Version
  1. 2016
Platform
  1. Windows
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
Rich (BB code):
 .AutoFilter Field:=7, Criteria1:="<" & dtStartTime, Operator:=xlOr, Criteria2:=">" & dtEndTime
Ah man, smh! Thank you, Sir.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,304
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You're welcome
 

Forum statistics

Threads
1,147,821
Messages
5,743,393
Members
423,792
Latest member
travisds

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top