Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort Dates
I have a worksheet with one column being dates in the format of 3/2, 4/25,
12/10, etc. As I added dates I could always sort from lowest (1/1) to highest (12/31). Now I can't seem to sort any dates that have been added in 2009. I do not have a year attached to these dates as they are birthdays and I need to sort by month/day regardless of year. Why all of a sudden the problem and how do I fix? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort Dates
Hi,
It happening because your dates are proper dates and the sort is including the year even though you cant see it. You need a helper column and put this formula in it =DAY(A1)&MONTH(A1) Drag down to the same length as your date and then selet both columns and sort on the kelper column which you can hide if you want. Mike "pete5440" wrote: I have a worksheet with one column being dates in the format of 3/2, 4/25, 12/10, etc. As I added dates I could always sort from lowest (1/1) to highest (12/31). Now I can't seem to sort any dates that have been added in 2009. I do not have a year attached to these dates as they are birthdays and I need to sort by month/day regardless of year. Why all of a sudden the problem and how do I fix? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort Dates
If no year associated, those are not dates but simply numbers entered as
text. Will sort as text only. A real Excel date is a serial number and sorts in that serial number order. January 16, 2008 is serial number 39829 Gord Dibben MS Excel MVP On Fri, 16 Jan 2009 14:38:02 -0800, pete5440 wrote: I have a worksheet with one column being dates in the format of 3/2, 4/25, 12/10, etc. As I added dates I could always sort from lowest (1/1) to highest (12/31). Now I can't seem to sort any dates that have been added in 2009. I do not have a year attached to these dates as they are birthdays and I need to sort by month/day regardless of year. Why all of a sudden the problem and how do I fix? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort Dates
Mike, First of all I'm not familiar with a "Helper Column". But will this
formula "fix" my issue, and will I have to keep the helper column and use it to sort my date column from here on? Gord, If my numbers are "text" why are my sort options "Oldest to Newest" and not "A-Z"? This sounds like the simpler method but how do I let excel know this is (supposed to be) text? "Gord Dibben" wrote: If no year associated, those are not dates but simply numbers entered as text. Will sort as text only. A real Excel date is a serial number and sorts in that serial number order. January 16, 2008 is serial number 39829 Gord Dibben MS Excel MVP On Fri, 16 Jan 2009 14:38:02 -0800, pete5440 wrote: I have a worksheet with one column being dates in the format of 3/2, 4/25, 12/10, etc. As I added dates I could always sort from lowest (1/1) to highest (12/31). Now I can't seem to sort any dates that have been added in 2009. I do not have a year attached to these dates as they are birthdays and I need to sort by month/day regardless of year. Why all of a sudden the problem and how do I fix? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort Dates
Oldest to Newest must be a 2007 function.
I am not familiar with it. I still believe you do not have real dates but perhaps 2007 treats these differently than earlier versions. Gord On Fri, 16 Jan 2009 16:04:00 -0800, pete5440 wrote: If my numbers are "text" why are my sort options "Oldest to Newest" and not "A-Z"? This sounds like the simpler method but how do I let excel know this is (supposed to be) text? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort Dates
Yes, this is 2007.
"Gord Dibben" wrote: Oldest to Newest must be a 2007 function. I am not familiar with it. I still believe you do not have real dates but perhaps 2007 treats these differently than earlier versions. Gord On Fri, 16 Jan 2009 16:04:00 -0800, pete5440 wrote: If my numbers are "text" why are my sort options "Oldest to Newest" and not "A-Z"? This sounds like the simpler method but how do I let excel know this is (supposed to be) text? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort Dates
If I highlight A2 (A1 is my column heading) the formula box shows 1/1/2008. I
have never set up the year and there is no formula set up as I have only ever added the month/day as "1/17" "Gord Dibben" wrote: Oldest to Newest must be a 2007 function. I am not familiar with it. I still believe you do not have real dates but perhaps 2007 treats these differently than earlier versions. Gord On Fri, 16 Jan 2009 16:04:00 -0800, pete5440 wrote: If my numbers are "text" why are my sort options "Oldest to Newest" and not "A-Z"? This sounds like the simpler method but how do I let excel know this is (supposed to be) text? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort Dates
What you see depends upon your Regional Settings
Mine are set for dd/mmm/yy I type 1/17 and formula bar shows jan 1, 2017 I type 17/1 and formula bar shows jan 17, 2009 1/12 shows dec 1, 2009 1/13 shows jan 1, 2013 See the pattern? If the month/day are ambiguous, Excel chooses from the Regional settings There is no 17th month so Excel thinks that must be the year. Gord On Sat, 17 Jan 2009 07:36:00 -0800, pete5440 wrote: If I highlight A2 (A1 is my column heading) the formula box shows 1/1/2008. I have never set up the year and there is no formula set up as I have only ever added the month/day as "1/17" "Gord Dibben" wrote: Oldest to Newest must be a 2007 function. I am not familiar with it. I still believe you do not have real dates but perhaps 2007 treats these differently than earlier versions. Gord On Fri, 16 Jan 2009 16:04:00 -0800, pete5440 wrote: If my numbers are "text" why are my sort options "Oldest to Newest" and not "A-Z"? This sounds like the simpler method but how do I let excel know this is (supposed to be) text? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort Dates
Actually the word "ambiguous" is incorrect.
More like if larger number is greater than 12 then Excel interprets that number as a year depending upon Regional settings being dd/mmm'yy Gord On Sat, 17 Jan 2009 08:37:53 -0800, Gord Dibben <gorddibbATshawDOTca wrote: What you see depends upon your Regional Settings Mine are set for dd/mmm/yy I type 1/17 and formula bar shows jan 1, 2017 I type 17/1 and formula bar shows jan 17, 2009 1/12 shows dec 1, 2009 1/13 shows jan 1, 2013 See the pattern? If the month/day are ambiguous, Excel chooses from the Regional settings There is no 17th month so Excel thinks that must be the year. Gord On Sat, 17 Jan 2009 07:36:00 -0800, pete5440 wrote: If I highlight A2 (A1 is my column heading) the formula box shows 1/1/2008. I have never set up the year and there is no formula set up as I have only ever added the month/day as "1/17" "Gord Dibben" wrote: Oldest to Newest must be a 2007 function. I am not familiar with it. I still believe you do not have real dates but perhaps 2007 treats these differently than earlier versions. Gord On Fri, 16 Jan 2009 16:04:00 -0800, pete5440 wrote: If my numbers are "text" why are my sort options "Oldest to Newest" and not "A-Z"? This sounds like the simpler method but how do I let excel know this is (supposed to be) text? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort dates | Excel Discussion (Misc queries) | |||
sort function for dates does not sort properly in Office 2007 Exc. | Excel Worksheet Functions | |||
sort dates by dd/mm/yyyyy | Excel Discussion (Misc queries) | |||
how do I sort a column of random dates into Consecutive dates | Excel Worksheet Functions | |||
sort dates | Excel Worksheet Functions |