ThePangloss
New Member
- Joined
- Jun 19, 2015
- Messages
- 40
Hey guys, I have a spreadsheet of assignments to students and I wanted to categorize them by assignment start date, and end date for each assignment. The problem is, the person who made the spreadsheet for me didn't organize the dates correctly so Assignment 1 might actually be Assignment 2 because the dates aren't in chronological order right now.
I need to organize everyones info in chronological order for each assignment, and some assignment both start on the same date so the preferable thing to do would be to use the earliest end date as Assignment 1 and the later one as Assignment 2.
An example of it would be like this
[TABLE="width: 500"]
<tbody>[TR]
[TD]NAME[/TD]
[TD]ID[/TD]
[TD]Start Assignment 1[/TD]
[TD]End Assignment 1[/TD]
[TD]Start Assignment 2[/TD]
[TD]End Assignment 2[/TD]
[/TR]
[TR]
[TD]Jimmy John[/TD]
[TD]922928[/TD]
[TD]9/20/2010[/TD]
[TD]6/12/2011[/TD]
[TD]9/20/2010[/TD]
[TD]4/20/2011[/TD]
[/TR]
[TR]
[TD]John Jones[/TD]
[TD]310020[/TD]
[TD]7/22/2014[/TD]
[TD]9/14/2014[/TD]
[TD]3/14/2012[/TD]
[TD]5/20/2012[/TD]
[/TR]
[TR]
[TD]Bob Marley[/TD]
[TD]929201[/TD]
[TD]3/29/2015[/TD]
[TD]6/20/2015[/TD]
[TD]6/21/2015[/TD]
[TD]7/11/2015[/TD]
[/TR]
[TR]
[TD]Roger Dukerer[/TD]
[TD]292910[/TD]
[TD]3/19/2009[/TD]
[TD]10/7/2009[/TD]
[TD]4/20/2007[/TD]
[TD]6/22/2008[/TD]
[/TR]
[TR]
[TD]Jane Small[/TD]
[TD]827102[/TD]
[TD]3/20/2010[/TD]
[TD]4/2/2010[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
So in this case, John Jones needs to have his two assignment dates switched, and so does Roger Dukerer since those two dates are both earlier. Jimmy John needs to get his assignment end dates switched because the earlier end date was 4/20/11 and the later one was 6/12/11 so the earlier one is Assignment 1 and the later one is Assignment 2.
Some people have more than 5 assignments, and some people only have one.
I tried using conditional formatting to try and find any dates that were less than the first two and sorted through those but I never got any matches probably because my formatting formula was wrong even when dragged down. If anyone has any ideas that'd be awesome!
I need to organize everyones info in chronological order for each assignment, and some assignment both start on the same date so the preferable thing to do would be to use the earliest end date as Assignment 1 and the later one as Assignment 2.
An example of it would be like this
[TABLE="width: 500"]
<tbody>[TR]
[TD]NAME[/TD]
[TD]ID[/TD]
[TD]Start Assignment 1[/TD]
[TD]End Assignment 1[/TD]
[TD]Start Assignment 2[/TD]
[TD]End Assignment 2[/TD]
[/TR]
[TR]
[TD]Jimmy John[/TD]
[TD]922928[/TD]
[TD]9/20/2010[/TD]
[TD]6/12/2011[/TD]
[TD]9/20/2010[/TD]
[TD]4/20/2011[/TD]
[/TR]
[TR]
[TD]John Jones[/TD]
[TD]310020[/TD]
[TD]7/22/2014[/TD]
[TD]9/14/2014[/TD]
[TD]3/14/2012[/TD]
[TD]5/20/2012[/TD]
[/TR]
[TR]
[TD]Bob Marley[/TD]
[TD]929201[/TD]
[TD]3/29/2015[/TD]
[TD]6/20/2015[/TD]
[TD]6/21/2015[/TD]
[TD]7/11/2015[/TD]
[/TR]
[TR]
[TD]Roger Dukerer[/TD]
[TD]292910[/TD]
[TD]3/19/2009[/TD]
[TD]10/7/2009[/TD]
[TD]4/20/2007[/TD]
[TD]6/22/2008[/TD]
[/TR]
[TR]
[TD]Jane Small[/TD]
[TD]827102[/TD]
[TD]3/20/2010[/TD]
[TD]4/2/2010[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
So in this case, John Jones needs to have his two assignment dates switched, and so does Roger Dukerer since those two dates are both earlier. Jimmy John needs to get his assignment end dates switched because the earlier end date was 4/20/11 and the later one was 6/12/11 so the earlier one is Assignment 1 and the later one is Assignment 2.
Some people have more than 5 assignments, and some people only have one.
I tried using conditional formatting to try and find any dates that were less than the first two and sorted through those but I never got any matches probably because my formatting formula was wrong even when dragged down. If anyone has any ideas that'd be awesome!
