Problems with dates and today()

[markdenovan]

I've got a cell and a column of cells formatted as dd/mm/yyyy

I've got an autofilter on the column of cells and use the single cell for the criteria.

So I have:

example 04/01/2004 dd/mm/yyyy

single cells formula =today()-1

Macro autofilter code:

Selection.AutoFilter Field:=1, Criteria1:=Range("menu!e8").Value

This works fine on my machine, but on another machine the autofilter doesn't find any matches. If I have a look at the filter, it is looking for 1/04/2004.

Its obviously a problem with date formatting.

Can anyone help me?

Is there a way to format the today() function ie today(dd/mm/yyyy)?

Many thanks,

Mark

 

[fairwinds]

Hi,

I don't think it is a formatting issue as formatting should not matter.

Check if:

- your cells have text instead of date

- it is a language version issue ( change to another date format and see if it is the correct date that is displayed)

 

[Richie(UK)]

Hi Mark,

Try amending the filter code to something along these lines:

Sub Filter()

    With Worksheets("Sheet1")
        .Range("C1").AutoFilter _
            Field:=1, Criteria1:="<=" & Format(.Range("A1"), "mm/dd/yyyy")
    End With

End Sub

HTH