Date Sorting

J

JayMan

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
 
B

Bernard Liengme

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
 
J

JayMan

Bernard
that works Thanks

Bernard Liengme said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top