Solved Date format filter (1 Viewer)

suryu

Member
Local time
Today, 12:16
Joined
Apr 3, 2020
Messages
54
Hi, i want to filter column having date format in "dd-mmm-yy"
Column range :=E1 and autofilter field:=5
 

June7

AWF VIP
Local time
Yesterday, 22:46
Joined
Mar 9, 2014
Messages
3,075
Code does not work? What happens?

What is actually in the cell? Text or a formatted number?
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:46
Joined
Sep 21, 2011
Messages
5,949
Hi, i want to filter column having date format in "dd-mmm-yy"
Column range :=E1 and autofilter field:=5
Use the Macro Recorder to get the basics, then amend to suit.
 

suryu

Member
Local time
Today, 12:16
Joined
Apr 3, 2020
Messages
54
Code does not work? What happens?

What is actually in the cell? Text or a formatted number?
Its formatted number

i am trying below code which is not working

(My column name is closing date)

strDate="06-Aug-10"

With Activesheet
.AutoFilterMode= False
.Range("E1").autoFilter
.Range("E1").AutoFilter Field:=5, Citeria1:=">" & Format(strDate, "dd-mmm-yy")

End With
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:46
Joined
Sep 21, 2011
Messages
5,949
Format is just that, a format.
Search for the date as a normal date.

Macro recorder would show you that.?
 

June7

AWF VIP
Local time
Yesterday, 22:46
Joined
Mar 9, 2014
Messages
3,075
Misspelling: Citeria1 should be Criteria1. Code works. Don't even need Format() function.
 

suryu

Member
Local time
Today, 12:16
Joined
Apr 3, 2020
Messages
54
Misspelling: Citeria1 should be Criteria1. Code works. Don't even need Format() function.
Criteria1 is fine but still filter is not working, in column blank is there but what i want date value to be filtered.
 
Last edited:

missinglinq

AWF VIP
Local time
Today, 02:46
Joined
Jun 20, 2003
Messages
6,369
Why are you posting a question about Excel VBA on an ACCESS forum?

All VBA is not created equal!

You should probably try a forum dedicated to MS Excel.

Linq ;0)>
 

Minty

AWF VIP
Local time
Today, 07:46
Joined
Jul 26, 2013
Messages
7,079
Your date field is stored as a number in excel as it would be in Access.
You need to filter by a date value not a string.

> DateValue (strDate)
 

pisorsisaac@gmail.co

Active member
Local time
Yesterday, 23:46
Joined
Mar 14, 2017
Messages
897
As Gasman suggested, this is a time to use the macro recorder and then adjust the resulting code to suit.
 

June7

AWF VIP
Local time
Yesterday, 22:46
Joined
Mar 9, 2014
Messages
3,075
I tested your code and it works for me - with or without Format() function. If you want to provide file for analysis, follow instructions at bottom of my post.
 

suryu

Member
Local time
Today, 12:16
Joined
Apr 3, 2020
Messages
54
Why are you posting a question about Excel VBA on an ACCESS forum?

All VBA is not created equal!

You should probably try a forum dedicated to MS Excel.

Linq ;0)>
Ok thanks it was not known to me
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom