Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help sorting DATES please..
I have a large amount of data, it's simply dates.
example 4/4/92 1/3/2007 5/6/1955 etc I want to display ONLY the month and then SORT only by the month. Basically I want to ignore the YEAR in the date and then sort. I've tried this several ways BUT it will not sort the numbers even if I format them. help? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help sorting DATES please..
Insert a helper column next to your dates and with the dates in (say) A1 down
put this in b1 and drag down =TEXT(A1,"mm/dd") Select your dates and this helper column and sort by the helper column. Mike "Crackles McFarly" wrote: I have a large amount of data, it's simply dates. example 4/4/92 1/3/2007 5/6/1955 etc I want to display ONLY the month and then SORT only by the month. Basically I want to ignore the YEAR in the date and then sort. I've tried this several ways BUT it will not sort the numbers even if I format them. help? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help sorting DATES please..
On Sat, 20 Oct 2007 05:46:00 -0700, Mike H
sayd the following: I cannot get it to sort in either direction. Can you help with that too? thanks Insert a helper column next to your dates and with the dates in (say) A1 down put this in b1 and drag down =TEXT(A1,"mm/dd") Select your dates and this helper column and sort by the helper column. Mike "Crackles McFarly" wrote: I have a large amount of data, it's simply dates. example 4/4/92 1/3/2007 5/6/1955 etc I want to display ONLY the month and then SORT only by the month. Basically I want to ignore the YEAR in the date and then sort. I've tried this several ways BUT it will not sort the numbers even if I format them. help? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help sorting DATES please..
On Sat, 20 Oct 2007 05:46:00 -0700, Mike H
sayd the following: July-60 July-71 December-73 July-83 October-83 July-86 August-87 June-90 August-90 August-90 March-91 July-92 July-92 August-92 September-92 September-92 April-93 I need to Sort by the MONTH and strip the YEAR. Then I need to simply Sort the column by Month only. I don't know if that makes any sense. thanks. Insert a helper column next to your dates and with the dates in (say) A1 down put this in b1 and drag down =TEXT(A1,"mm/dd") Select your dates and this helper column and sort by the helper column. Mike "Crackles McFarly" wrote: I have a large amount of data, it's simply dates. example 4/4/92 1/3/2007 5/6/1955 etc I want to display ONLY the month and then SORT only by the month. Basically I want to ignore the YEAR in the date and then sort. I've tried this several ways BUT it will not sort the numbers even if I format them. help? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help sorting DATES please..
In an adjacent column enter =MONTH(A1) and drag/copy down.
Sort by that column. Gord Dibben MS Excel MVP On Sat, 20 Oct 2007 09:06:52 -0400, Crackles McFarly wrote: On Sat, 20 Oct 2007 05:46:00 -0700, Mike H sayd the following: July-60 July-71 December-73 July-83 October-83 July-86 August-87 June-90 August-90 August-90 March-91 July-92 July-92 August-92 September-92 September-92 April-93 I need to Sort by the MONTH and strip the YEAR. Then I need to simply Sort the column by Month only. I don't know if that makes any sense. thanks. Insert a helper column next to your dates and with the dates in (say) A1 down put this in b1 and drag down =TEXT(A1,"mm/dd") Select your dates and this helper column and sort by the helper column. Mike "Crackles McFarly" wrote: I have a large amount of data, it's simply dates. example 4/4/92 1/3/2007 5/6/1955 etc I want to display ONLY the month and then SORT only by the month. Basically I want to ignore the YEAR in the date and then sort. I've tried this several ways BUT it will not sort the numbers even if I format them. help? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help sorting DATES please..
On Sat, 20 Oct 2007 07:57:23 -0700, Gord Dibben <gorddibbATshawDOTca
sayd the following: In an adjacent column enter =MONTH(A1) and drag/copy down. Sort by that column. Gord Dibben MS Excel MVP That gives a number result, like 12 in place of December. Also I cannot sort, the function doesn't work on the MONTH(A1) Is their some setting that is not allowing me to Sort? I'm using WORD 97 btw. thanks. On Sat, 20 Oct 2007 09:06:52 -0400, Crackles McFarly wrote: On Sat, 20 Oct 2007 05:46:00 -0700, Mike H sayd the following: July-60 July-71 December-73 July-83 October-83 July-86 August-87 June-90 August-90 August-90 March-91 July-92 July-92 August-92 September-92 September-92 April-93 I need to Sort by the MONTH and strip the YEAR. Then I need to simply Sort the column by Month only. I don't know if that makes any sense. thanks. Insert a helper column next to your dates and with the dates in (say) A1 down put this in b1 and drag down =TEXT(A1,"mm/dd") Select your dates and this helper column and sort by the helper column. Mike "Crackles McFarly" wrote: I have a large amount of data, it's simply dates. example 4/4/92 1/3/2007 5/6/1955 etc I want to display ONLY the month and then SORT only by the month. Basically I want to ignore the YEAR in the date and then sort. I've tried this several ways BUT it will not sort the numbers even if I format them. help? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help sorting DATES please..
Why do you need it formatted if all you're doing is sorting by?
Format column A to "mmmm" to see just the months in that column then hide B if you don't want to see the numbers I have no trouble sorting on column B tested with the dates you supplied below. Select both columns before sorting by the MONTH column I hope you're not using WORD 97.........this is an Excel news group. Gord On Sat, 20 Oct 2007 11:22:34 -0400, Crackles McFarly wrote: On Sat, 20 Oct 2007 07:57:23 -0700, Gord Dibben <gorddibbATshawDOTca sayd the following: In an adjacent column enter =MONTH(A1) and drag/copy down. Sort by that column. Gord Dibben MS Excel MVP That gives a number result, like 12 in place of December. Also I cannot sort, the function doesn't work on the MONTH(A1) Is their some setting that is not allowing me to Sort? I'm using WORD 97 btw. thanks. On Sat, 20 Oct 2007 09:06:52 -0400, Crackles McFarly wrote: On Sat, 20 Oct 2007 05:46:00 -0700, Mike H sayd the following: July-60 July-71 December-73 July-83 October-83 July-86 August-87 June-90 August-90 August-90 March-91 July-92 July-92 August-92 September-92 September-92 April-93 I need to Sort by the MONTH and strip the YEAR. Then I need to simply Sort the column by Month only. I don't know if that makes any sense. thanks. Insert a helper column next to your dates and with the dates in (say) A1 down put this in b1 and drag down =TEXT(A1,"mm/dd") Select your dates and this helper column and sort by the helper column. Mike "Crackles McFarly" wrote: I have a large amount of data, it's simply dates. example 4/4/92 1/3/2007 5/6/1955 etc I want to display ONLY the month and then SORT only by the month. Basically I want to ignore the YEAR in the date and then sort. I've tried this several ways BUT it will not sort the numbers even if I format them. help? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help sorting DATES please..
On Sat, 20 Oct 2007 09:13:41 -0700, Gord Dibben <gorddibbATshawDOTca
sayd the following: Why do you need it formatted if all you're doing is sorting by? Format column A to "mmmm" to see just the months in that column then hide B if you don't want to see the numbers I have no trouble sorting on column B tested with the dates you supplied below. Select both columns before sorting by the MONTH column I hope you're not using WORD 97.........this is an Excel news group. i SWEAR it will not sort in order of month when I do what you've said to do. Maybe you could give me some more hints, I don't know how to do this it's obvious. It was a mistake, yes I am of course using EXCEL 97 not WORD 97 thanks for helping. On Sat, 20 Oct 2007 11:22:34 -0400, Crackles McFarly wrote: On Sat, 20 Oct 2007 07:57:23 -0700, Gord Dibben <gorddibbATshawDOTca sayd the following: In an adjacent column enter =MONTH(A1) and drag/copy down. Sort by that column. Gord Dibben MS Excel MVP That gives a number result, like 12 in place of December. Also I cannot sort, the function doesn't work on the MONTH(A1) Is their some setting that is not allowing me to Sort? I'm using WORD 97 btw. thanks. On Sat, 20 Oct 2007 09:06:52 -0400, Crackles McFarly wrote: On Sat, 20 Oct 2007 05:46:00 -0700, Mike H sayd the following: July-60 July-71 December-73 July-83 October-83 July-86 August-87 June-90 August-90 August-90 March-91 July-92 July-92 August-92 September-92 September-92 April-93 I need to Sort by the MONTH and strip the YEAR. Then I need to simply Sort the column by Month only. I don't know if that makes any sense. thanks. Insert a helper column next to your dates and with the dates in (say) A1 down put this in b1 and drag down =TEXT(A1,"mm/dd") Select your dates and this helper column and sort by the helper column. Mike "Crackles McFarly" wrote: I have a large amount of data, it's simply dates. example 4/4/92 1/3/2007 5/6/1955 etc I want to display ONLY the month and then SORT only by the month. Basically I want to ignore the YEAR in the date and then sort. I've tried this several ways BUT it will not sort the numbers even if I format them. help? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help sorting DATES please..
Using your dates below in column A formatted as mmmm and =MONTH(A1) in column B
and copying down I get this returned. July 7 July 7 December 12 July 7 October 10 July 7 August 8 June 6 August 8 August 8 March 3 July 7 July 7 August 8 September 9 September 9 After sorting both columns together by column B if get March 3 June 6 July 7 July 7 July 7 July 7 July 7 July 7 August 8 August 8 August 8 August 8 September 9 September 9 October 10 December 12 Are you sure your dates are real dates and not text? Gord On Sat, 20 Oct 2007 15:10:34 -0400, Crackles McFarly wrote: July-60 July-71 December-73 July-83 October-83 July-86 August-87 June-90 August-90 August-90 March-91 July-92 July-92 August-92 September-92 September-92 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help sorting DATES please..
On Sat, 20 Oct 2007 12:45:06 -0700, Gord Dibben <gorddibbATshawDOTca
sayd the following: Using your dates below in column A formatted as mmmm and =MONTH(A1) in column B and copying down I get this returned. July 7 July 7 December 12 July 7 October 10 July 7 August 8 June 6 August 8 August 8 March 3 July 7 July 7 August 8 September 9 September 9 After sorting both columns together by column B if get March 3 June 6 July 7 July 7 July 7 July 7 July 7 July 7 August 8 August 8 August 8 August 8 September 9 September 9 October 10 December 12 Are you sure your dates are real dates and not text? Gord No it's formatted as a DATE, I guess I'll figure this old some day but not this day. I feel slightly retarded for not know how to do something this simple. I "do" appreciate your patience and help though. On Sat, 20 Oct 2007 15:10:34 -0400, Crackles McFarly wrote: July-60 July-71 December-73 July-83 October-83 July-86 August-87 June-90 August-90 August-90 March-91 July-92 July-92 August-92 September-92 September-92 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help sorting DATES please..
Just because it is formatted as a date doesn't mean it is a date.
When you change column A format to General what do you see? If real dates you should see 5-digit numbers like 33298 33025 22098 26115 30498 Do you get numbers from the =MONTH() formula as I did in column B? Gord On Sat, 20 Oct 2007 16:06:30 -0400, Crackles McFarly wrote: No it's formatted as a DATE, I guess I'll figure this old some day but not this day. I feel slightly retarded for not know how to do something this simple. I "do" appreciate your patience and help though. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help sorting DATES please..
On Sat, 20 Oct 2007 14:00:23 -0700, Gord Dibben <gorddibbATshawDOTca
sayd the following: Just because it is formatted as a date doesn't mean it is a date. When you change column A format to General what do you see? If real dates you should see 5-digit numbers like 33298 33025 22098 26115 30498 Do you get numbers from the =MONTH() formula as I did in column B? I see 5 digit numbers as you do. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Dates | Excel Discussion (Misc queries) | |||
Sorting Dates | Excel Worksheet Functions | |||
Sorting Dates | Excel Worksheet Functions | |||
Sorting dates | Excel Worksheet Functions | |||
Sorting dates | Excel Worksheet Functions |