Autofilter between two times: Easier than it sounds

Kentetsu

Well-known Member
Joined
Jan 22, 2004
Messages
520
I'm fairly certain that this has to be a formatting issue of some kind.

When stepping through the code, it correctly identifies "TimeToSearch1" and "TimeToSearch2" as the times to filter for.

Code:
With Worksheets("PII Yields")
        If .FilterMode Then .Range("A2:F2").AutoFilter
        .Range("A2:F2").AutoFilter Field:=1, _
                    Criteria1:=">=" & TimeToSearch1, _
                    Operator:=xlAnd, _
                    Criteria2:="<=" & TimeToSearch2
End With

I have formatted the Target Times to match the formatting on the spreadsheet I'm trying to manipulate but it doesn't seem to make any difference. The Target Times show as time only, while the spreadsheet shows a date/time combination. So, I'm guessing that I need to somehow change the formatting at one end or the other so that they finally match. Just not sure how to get there.

There are a couple of steps before this filter process which may have something to do with the issue:

1. Filter spreadsheet by selected date.
2. Copy results and paste to new sheet as Values.
3. Format Column A (Dates) to .Range("A:A").NumberFormat = "HH:MM;@"

Thanks for your time...
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Still thinking on this. The Spreadsheet I'm trying to manipulate has the date Column in a Date/Time format.

The Spreadsheet that is used to manipulate it asks 3 questions: Date, Start Time, and End Time (each in a separate cell). Then the VBA filters for the date, formats the column to Time (although if clicking on the cell you will still see Date/Time in the Formula bar), then attempts to filter between the two time periods (and falls flat on its face with zero results returned).

Maybe if I format my original filter so that, instead of filtering for only the date, it filters for the actual Date & Time range I'm after in one shot. Then the Question would be, how do I combine a Date in a cell with a Time in another cell so that it matches the formatting in the target spreadsheet?

I think I need a cold beer and a couple of days sleep... :)
 
Upvote 0
Perhaps you could post a sample of your data? Tools for doing so in my signature.

/AJ
 
Upvote 0

Excel 2010
ABCD
2LoadedSKURacksOven
300:2304060116
400:271214988
500:461236399
600:581236361
701:001236362
803:4416307518
904:0816460917
1004:101646095
1113:3812950918
1213:5312950917
1314:041646074
1414:121646075
1514:201750292
1614:271750291
1714:331750299
1816:3902902107
1917:091295096
2021:5716307918
2122:2212950917
PII Yields




Attempting to sort on Column A, based on a time span. Data in Column A appears as time values, but are actually like this: 11/17/2014 12:23:28 AM (data from A3)

Thanks...
 
Upvote 0
If you have date and time in the same column in the source data, formatting won't help. You'll need to filter for >= date and start time and <= date and end time.
 
Upvote 0
Thanks RoryA.

So, my choices as I see them are:

1. Strip the date from the column, leaving only the time information
2. Find a way to correctly format things at the input end, combining the Date from one cell with the Time from the other, and use that as the search parameter.
 
Upvote 0
If you have a date and a time, you can simply add them together to get the datetime value you need.
 
Upvote 0
As in concatenate the two values into one cell, or do you mean combining them with VBA? Thanks for your time/patience. :)
 
Upvote 0
I meant in VBA since you are getting input from the user in VBA.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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
Back
Top