Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? -- clcnewtoaccess |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem custom sorting a date in an excel file
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 On Fri, 16 Jan 2009 07:02:04 -0800, clcnewtoaccess wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
date format problem from opening dbf file in excel 2007 | Excel Discussion (Misc queries) | |||
Automatic sorting (giving max and min) based on custom sorting lis | Excel Worksheet Functions | |||
Problem with sorting by date columns | Excel Discussion (Misc queries) | |||
problem with date sorting | Excel Worksheet Functions | |||
Date sorting problem | Excel Discussion (Misc queries) |