problem custom sorting a date in an excel file



I have an Access report that is output to an excel file. I open the excel
file and do a data create list so I can do a custom sort. The field I am
trying to sort is a date field it is formatted "16-Jan-2009". I select
custom and contains and type in "Jan" and click OK. I get no sort at all, it
comes up blank. I have tried formatting the date 2 ways and it still comes
up blank. What am I doing wrong?

Gord Dibben

I think you are trying to "filter" not "sort". You cannot sort by one day.

Dates in Excel are just a serial number like today.....January 16, 2009 is

You can see that number if you format the date to General.

How the date looks as 16-Jan-2009 is due to formatting of that serial

For more on Excel date handling see Chip Pearson's site.

Contains "Jan" will not you have found out.

If your "dates" were text you could find "Jan".

Or break out the month and day in helper columns =MONTH(cellref) and
=DAY(cellref) will return a number.........1 for January, 16 for day.

Filter for those numbers.

Or simply pick the date from the dropdown list.

Gord Dibben MS Excel MVP

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question