ExcelBanter - Problem with date formatting

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Problem with date formatting (https://www.excelbanter.com/excel-programming/443373-problem-date-formatting.html)

Problem with date formatting

Hi
Below is part of a Macro to Autofilter by a date in cell "N1".

Range("N1").Select
Selection.NumberFormat = "dd/mm/yyyy;@"

Columns("A:A").Select
Selection.NumberFormat = "dd/mm/yyyy;@"

Range("A2").AutoFilter Field:=1, Criteria1:="<" & Range("n1"),
Operator:=xlAnd

I formatted the values in N1 and column A to be the same date format
to hopefully avoid problems, however the filter does not work.

Dates like 13/07/2010 and 12/07/2010 get formatted to
13/07/10 and 07/12/2010 respectively (both should be filtered out
as the date in N1 is greater than both dates however only the
07/12/2010 gets filtered)

Is there another way to format these dates to make my filter work.

Much appreciated

Eddie

Problem with date formatting

Maybe try

Range("A2").AutoFilter Field:=1, _
Criteria1:="<" & Format(Range("N1"),Range("A2").NumberFormat), _
Operator:=xlAnd

--

HTH

Bob

"webels" wrote in message
...
Hi
Below is part of a Macro to Autofilter by a date in cell "N1".

Range("N1").Select
Selection.NumberFormat = "dd/mm/yyyy;@"

Columns("A:A").Select
Selection.NumberFormat = "dd/mm/yyyy;@"

Range("A2").AutoFilter Field:=1, Criteria1:="<" & Range("n1"),
Operator:=xlAnd

I formatted the values in N1 and column A to be the same date format
to hopefully avoid problems, however the filter does not work.

Dates like 13/07/2010 and 12/07/2010 get formatted to
13/07/10 and 07/12/2010 respectively (both should be filtered out
as the date in N1 is greater than both dates however only the
07/12/2010 gets filtered)

Is there another way to format these dates to make my filter work.

Much appreciated

Eddie

Problem with date formatting

As a double safety

Maybe try

Range("A2").AutoFilter Field:=1, _
Criteria1:="<" &
Format(CDate(Range("N1").Value),Range("A2").Number Format), _
Operator:=xlAnd

--

HTH

Bob

"Bob Phillips" wrote in message
...
Maybe try

Range("A2").AutoFilter Field:=1, _
Criteria1:="<" & Format(Range("N1"),Range("A2").NumberFormat), _
Operator:=xlAnd

--

HTH

Bob

"webels" wrote in message
...
Hi
Below is part of a Macro to Autofilter by a date in cell "N1".

Range("N1").Select
Selection.NumberFormat = "dd/mm/yyyy;@"

Columns("A:A").Select
Selection.NumberFormat = "dd/mm/yyyy;@"

Range("A2").AutoFilter Field:=1, Criteria1:="<" & Range("n1"),
Operator:=xlAnd

I formatted the values in N1 and column A to be the same date format
to hopefully avoid problems, however the filter does not work.

Dates like 13/07/2010 and 12/07/2010 get formatted to
13/07/10 and 07/12/2010 respectively (both should be filtered out
as the date in N1 is greater than both dates however only the
07/12/2010 gets filtered)

Is there another way to format these dates to make my filter work.

Much appreciated

Eddie

Problem with date formatting

On Jul 20, 9:34*am, "Bob Phillips" wrote:
As a double safety

Maybe try

Range("A2").AutoFilter Field:=1, _
* * * * Criteria1:="<" &
Format(CDate(Range("N1").Value),Range("A2").Number Format), _
* * * * Operator:=xlAnd

--

HTH

Bob

"Bob Phillips" wrote in message

...

Maybe try

Range("A2").AutoFilter Field:=1, _
* * * *Criteria1:="<" & Format(Range("N1"),Range("A2").NumberFormat), _
* * * *Operator:=xlAnd

--

HTH

Bob

"webels" wrote in message
....
Hi
Below is part of a Macro to Autofilter by a date in cell "N1".

Range("N1").Select
* *Selection.NumberFormat = "dd/mm/yyyy;@"

* *Columns("A:A").Select
* *Selection.NumberFormat = "dd/mm/yyyy;@"

* Range("A2").AutoFilter Field:=1, Criteria1:="<" & Range("n1"),
Operator:=xlAnd

I formatted the values in N1 and column A to be the same date format
to hopefully avoid problems, however the filter does not work.

Dates like 13/07/2010 and 12/07/2010 get formatted to
13/07/10 * and * 07/12/2010 respectively (both should be filtered out
as the date in N1 is greater than both dates however only the
07/12/2010 gets filtered)

Is there another way to format these dates to make my filter work.

Much appreciated

Eddie

Thanks Bob
Will try this and see

Problem with date formatting

On Jul 20, 5:34*pm, "Bob Phillips" wrote:
As a double safety

Maybe try

Range("A2").AutoFilter Field:=1, _
* * * * Criteria1:="<" &
Format(CDate(Range("N1").Value),Range("A2").Number Format), _
* * * * Operator:=xlAnd

--

HTH

Bob

"Bob Phillips" wrote in message

...

Maybe try

Range("A2").AutoFilter Field:=1, _
* * * *Criteria1:="<" & Format(Range("N1"),Range("A2").NumberFormat), _
* * * *Operator:=xlAnd

--

HTH

Bob

"webels" wrote in message
....
Hi
Below is part of a Macro to Autofilter by a date in cell "N1".

Range("N1").Select
* *Selection.NumberFormat = "dd/mm/yyyy;@"

* *Columns("A:A").Select
* *Selection.NumberFormat = "dd/mm/yyyy;@"

* Range("A2").AutoFilter Field:=1, Criteria1:="<" & Range("n1"),
Operator:=xlAnd

I formatted the values in N1 and column A to be the same date format
to hopefully avoid problems, however the filter does not work.

Dates like 13/07/2010 and 12/07/2010 get formatted to
13/07/10 * and * 07/12/2010 respectively (both should be filtered out
as the date in N1 is greater than both dates however only the
07/12/2010 gets filtered)

Is there another way to format these dates to make my filter work.

Much appreciated

Eddie- Hide quoted text -

- Show quoted text -

Hi Bob
This didn't seem to work, I am still left with dates after the date in
"N1". I should explain myself better. I ahve dates in Column A and I
am trying to delete rows that have dates from Today() minus 56 days
inclusive.

Much appreciated
Eddie

Problem with date formatting