problem custom sorting a date in an excel file

[clcnewtoaccess]

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
39829

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
number.

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

http://www.cpearson.com/excel/datetime.htm#SerialDates

Contains "Jan" will not work...........as 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