Autofilter on Dates problem

  • Thread starter Thread starter The Cube
  • Start date Start date
T

The Cube

Hi all
I have an autofiltered table containing a column of dates with number format
dd/mm/yyyy. The entries are populated by "pick from list" data validation.

Several of the entries in this column appear as 05/04/2003 and further
research shows that they have numerical value 37716.

If I autofilter on this field setting criteria to:
Equal to: 37716
then all rows disappear ... no matches found.

If I autofilter setting criteria to:
Equal to: 05/04/2003
then the predicted rows reappear.

If I autofilter setting the criteria to:
Greater than or equal to: 37716
AND
Less than or equal to: 37716
then the predicted rows are displayed.
How is this behaviour different from a simple
Equal to: 37716
?

I need to resolve this as I have a macro that filters on this field by
reference to its numerical value with "Equal to" as the logical operator,
and I cannot understand why it filters out the whole population.

-Cube
 
Can you not insert an exrtra extra column next to the date column and set it
equal to its neighbor and then format it as general and then hide the
column, use your macro on the new column? You are trying to do 2 different
numbering systems, yes in general format 5/4/2003 does equal 37745 but date
format and general are different! Also my system shows 5/4/2003 as 37745 not
37716 as you state!!
 
Since you're doing this in a macro, try changing the format of that date column
to General. The apply your format using the date's serial number (37716).

Then change the format the column back to the date format you like.
 
Thanks Intruder

The discrepancy in the numerical values is because I am formatting on
UK-style dates, so 5/4/2003 is 5 April not 4 May, but that is irrelevant to
the problem.

I shall try your solution, but if my test of "<= and >=" can be relied upon
always to work it seems more efficient to stick with that. The main trouble
is that I do not understand WHY that works but a simple test of "=" does
not, and until I understand the why of it, it may be dangerous to assume
that the "<= and >=" test will always work. The same of course applies to
any other workaround.

-Cube
 
Dave Peterson said:
Since you're doing this in a macro, try changing the format of that date column
to General. The apply your format using the date's serial number (37716).

Then change the format the column back to the date format you like.
Click to expand...

Thanks Dave. I percieve a problem with this workaround; additional rows
will be added to the table periodically and I (or the user) may have to
apply that workaround on each such occasion.

Provided that the test of "<= and >=" can be relied upon always to work then
I may stick with that. I haven't yet come across an example where it fails
but that is not conclusive unless it can be shown that theoretically it
should work while testing for "=" should fail. At present I am not happy on
that point.

-Cube
 
Sometimes the autofilter via code will behave nicer if you use the same format
as shown on the worksheet:

Option Explicit
Sub testme()
Dim myRng As Range
Dim myDateFormat As String
With Worksheets("sheet1")
Set myRng = .Range("e:e")
myDateFormat = .Range("e2").NumberFormat
myRng.AutoFilter field:=1, _
Criteria1:=">=" & Format(DateSerial(2003, 10, 11), myDateFormat)
End With
End Sub
 
Back
Top