Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering on dates
Name Date Of Hire
Joe 1/1/2007 Bob 3/4/2007 bob 5/10/2007 Bill 7/3/2007 Tom 8/6/2007 Mary 11/15/2007 I want to show all people and hire dates for hire dates after 7/1/2007. What format do the dates need to be in, what is the procedure/syntax? Thanks, Alan Beban |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering on dates
Filtering dates in code can be a miserable experience.
I used to screw around with things like: ...., criteria1:=clng(dateserial(2007,7,1), ... Or by picking up the numberformat from the first cell in the field that has a date. But Ron de Bruin pointed me to a way that works for him (he can have a problem with international issues). This is from "Excel 2002 VBA Programmer's Reference" Written by John Green, Stephen Bullen, Rob Bovey and Robert Rosenberg http://www.oaltd.co.uk:80/ExcelProgR...rogRefCh22.htm Search for "Range.AutoFilter" and you'll see this note: Range.AutoFilter The AutoFilter method of a Range object is a very curious beast. We are forced to pass it strings for its filter criteria and hence must be aware of its string handling behaviour. The criteria string consists of an operator (=, , <, = etc.) followed by a value. If no operator is specified, the "=" operator is assumed. The key issue is that when using the "=" operator, AutoFilter performs a textual match, while using any other operator results in a match by value. This gives us problems when trying to locate exact matches for dates and numbers. If we use "=", Excel matches on the text that is displayed in the cell, i.e. the formatted number. As the text displayed in a cell will change with different regional settings and Windows language version, it is impossible for us to create a criteria string that will locate an exact match in all locales. There is a workaround for this problem. When using any of the other filter criteria, Excel plays by the rules and interprets the criteria string according to US formats. Hence, a search criterion of "=02/01/2001" will find all dates on or after 1st Feb, 2001, in all locales. We can use this to match an exact date by using two AutoFilter criteria. The following code will give an exact match on 1st Feb, 2001 and will work in any locale: Range("A1:D200").AutoFilter 2, "=02/01/2001", xlAnd, "<=02/01/2001" ============= I haven't actually had to use it in real life, but those guys are very, very smart and I trust them <bg. Maybe you could post back with your results. Alan Beban wrote: Name Date Of Hire Joe 1/1/2007 Bob 3/4/2007 bob 5/10/2007 Bill 7/3/2007 Tom 8/6/2007 Mary 11/15/2007 I want to show all people and hire dates for hire dates after 7/1/2007. What format do the dates need to be in, what is the procedure/syntax? Thanks, Alan Beban -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering on dates
Hi Alan,
I am a bit confused as to why you have posted this question. The date 11/15/2007 suggests you are working with US dates which implies your system is also US. AFAIK filtering US system dates with any operator except perhaps "=" does not present a problem. I would have thought a recorded macro will give something you can simply adapt. Start with if .autofiltermode then ..autofilter (to clear), then .autofilter again with the requisite fields & criteria). My system is International so I can't immediately verify, but what problem is it you have. If you need to cater for International dates and/or looking to filter "=" the extracts from the book as quoted by Dave are indeed highly relevant. I don't think it quite gives the whole story in some respects, eg re the string textural search; also while date-order, as determined by cell numberformat, may be US or Int, other aspects of the format are relevant. In addition, depending on format there is a way to filter = date with a single =, without need to do the = And <= even with Int' dates & system. FWIW the same issue with international dates and = similarly, though not exactly, affects some other things. Eg 'Find', which requires a subtle variation of the convert to US date fix. Regards, Peter T "Alan Beban" wrote in message ... Name Date Of Hire Joe 1/1/2007 Bob 3/4/2007 bob 5/10/2007 Bill 7/3/2007 Tom 8/6/2007 Mary 11/15/2007 I want to show all people and hire dates for hire dates after 7/1/2007. What format do the dates need to be in, what is the procedure/syntax? Thanks, Alan Beban |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filtering Dates | Excel Discussion (Misc queries) | |||
filtering dates | Excel Worksheet Functions | |||
filtering dates | Excel Worksheet Functions | |||
Filtering with dates | Excel Discussion (Misc queries) | |||
Filtering of dates | Excel Programming |