Problem with Auto Filtering Dates

[meadt]

Hi Guys,

I am trying to use the custom option on the autofilter tool to filter a column of dates. I need it to filter every entry that equals or is more recent than a date that I put in. The date format is dd.mm.yyyy. The filter doenst seem to filter them correctly or filters them all out. I thought it may be in American date format, if so how would I change it back?

thanks in advance,

T.

 

[Brianwarnock]

I just tried this and it works fine for me.
You may have to post you spreadsheet although if its 2007 I wont be able to help

Brian

 

[meadt]

Hi Brian,

Thanks for helping me, I've attached a test file that Ive been playing around with. The format of the date is generated by a VBA function that I dont have access to so I would need to stick to the current date format.

cheers,

T.

 

[Brianwarnock]

OK problem solved.
Although you can have the custom format dd.mm.yyyy for Excel to recognise it as a date you must enter it as dd/mm/yyyy, and having done that in the filter the date must also be entered as dd/mm/yyyy

In my previous quick look i think I was doing a Text filter!!


Brian

 

[Brianwarnock]

Yes it thinks that you have Text data not dates, to prove it do a sort.

I would edit the data into another column using the Date function to create Dates.
eg =DATE(RIGHT(E2,4),MID(E2,4,2),LEFT(E2,2))

Brian

 

[meadt]

Thanks for your suggestion, it worked.

Cheers,

T.