Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Sorting
I have a worksheet that has multiple columns with dates in random order. I
need to sort the dates by descending order first count them and then group them by period. For example Task 1 Task 2 Task 3 1/5/2006 1/11/2006 1/5/2006 1/17/2006 1/12/2006 1/11/2006 1/26/2006 1/16/2006 2/6/2006 2/1/2006 1/16/2006 2/4/2005 2/22/2006 1/16/2006 2/4/2005 3/8/2006 1/16/2006 2/4/2005 3/13/2006 1/16/2006 2/17/2005 3/14/2006 1/18/2006 2/17/2005 3/14/2006 1/22/2006 2/18/2005 3/16/2006 1/22/2006 1/5/2006 3/16/2006 1/31/2006 1/16/2006 3/16/2006 2/6/2006 1/31/2006 3/16/2006 2/6/2006 2/6/2006 3/22/2006 2/6/2006 2/6/2006 3/23/2006 2/6/2006 3/11/2006 3/28/2006 2/6/2006 3/4/2005 I need something that looks like Jan 1-15 Jan 15-31 Feb 1-15 Feb 15-28 Mar 1-15 Task 1 1 2 1 1 4 Task 2 2 8 5 Task 3 3 6 5 2 Any help on this is appreciated |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Sorting
For Jan 1 to Jan 15 for first column (assumed to be column A and in rows
2:17) Jan 1 to Jan 15: =SUMPRODUCT(--(A2:A17=DATE(2006,1,1)),--(A2:A17<=DATE(2006,1,15))) Jan 16:Jan 31 (be careful not to count Jan 15 twice) =SUMPRODUCT(--(A2:A17=DATE(2006,1,16)),--(A2:A17<=DATE(2006,1,31))) If you put dates in cells such as G1 and G2 =SUMPRODUCT(--(A2:A17=G1)),--(A2:A17<=G2)) best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "JayMan" wrote in message ... I have a worksheet that has multiple columns with dates in random order. I need to sort the dates by descending order first count them and then group them by period. For example Task 1 Task 2 Task 3 1/5/2006 1/11/2006 1/5/2006 1/17/2006 1/12/2006 1/11/2006 1/26/2006 1/16/2006 2/6/2006 2/1/2006 1/16/2006 2/4/2005 2/22/2006 1/16/2006 2/4/2005 3/8/2006 1/16/2006 2/4/2005 3/13/2006 1/16/2006 2/17/2005 3/14/2006 1/18/2006 2/17/2005 3/14/2006 1/22/2006 2/18/2005 3/16/2006 1/22/2006 1/5/2006 3/16/2006 1/31/2006 1/16/2006 3/16/2006 2/6/2006 1/31/2006 3/16/2006 2/6/2006 2/6/2006 3/22/2006 2/6/2006 2/6/2006 3/23/2006 2/6/2006 3/11/2006 3/28/2006 2/6/2006 3/4/2005 I need something that looks like Jan 1-15 Jan 15-31 Feb 1-15 Feb 15-28 Mar 1-15 Task 1 1 2 1 1 4 Task 2 2 8 5 Task 3 3 6 5 2 Any help on this is appreciated |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Sorting
Bernard
that works Thanks "Bernard Liengme" wrote: For Jan 1 to Jan 15 for first column (assumed to be column A and in rows 2:17) Jan 1 to Jan 15: =SUMPRODUCT(--(A2:A17=DATE(2006,1,1)),--(A2:A17<=DATE(2006,1,15))) Jan 16:Jan 31 (be careful not to count Jan 15 twice) =SUMPRODUCT(--(A2:A17=DATE(2006,1,16)),--(A2:A17<=DATE(2006,1,31))) If you put dates in cells such as G1 and G2 =SUMPRODUCT(--(A2:A17=G1)),--(A2:A17<=G2)) best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "JayMan" wrote in message ... I have a worksheet that has multiple columns with dates in random order. I need to sort the dates by descending order first count them and then group them by period. For example Task 1 Task 2 Task 3 1/5/2006 1/11/2006 1/5/2006 1/17/2006 1/12/2006 1/11/2006 1/26/2006 1/16/2006 2/6/2006 2/1/2006 1/16/2006 2/4/2005 2/22/2006 1/16/2006 2/4/2005 3/8/2006 1/16/2006 2/4/2005 3/13/2006 1/16/2006 2/17/2005 3/14/2006 1/18/2006 2/17/2005 3/14/2006 1/22/2006 2/18/2005 3/16/2006 1/22/2006 1/5/2006 3/16/2006 1/31/2006 1/16/2006 3/16/2006 2/6/2006 1/31/2006 3/16/2006 2/6/2006 2/6/2006 3/22/2006 2/6/2006 2/6/2006 3/23/2006 2/6/2006 3/11/2006 3/28/2006 2/6/2006 3/4/2005 I need something that looks like Jan 1-15 Jan 15-31 Feb 1-15 Feb 15-28 Mar 1-15 Task 1 1 2 1 1 4 Task 2 2 8 5 Task 3 3 6 5 2 Any help on this is appreciated |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting by Date | New Users to Excel | |||
Sorting by date | Excel Discussion (Misc queries) | |||
Sorting By Date | Excel Discussion (Misc queries) | |||
sorting date " MMM-YY" (Jan-05) | Excel Discussion (Misc queries) | |||
Sorting by date when the date isn't exact | Excel Discussion (Misc queries) |