Excel date conundrum 3

[carrr]

I'll try and be as clear as possible on this one...

I have a user - (who is afraid of taking the easy way out and using Access for this) - who has a large amount of data with DATE as the focus. She would like to take the dates (ex: 3/3/2003) and essentially strip away the year (the dates in question range from 1989 to 2003), leaving an array of data that look like 3/3 and then sort by month.
(I can easily change the format, but I can't take away the fact that Excel is still seeing the year). The desired end result is that all January dates are clustered together, all February dates are clustered together, (regardless of what year they occurred in)... etc, etc.

What am I missing?
Thanks in advance...

 

[SkipVought]

Carr,

Try a PivotTable on the Dates and GROUP by Month WITHOUT YEAR.

BUT...

you MUST keep the DATES as DATES

AND...

they must ALL be DATES (no blanks or text)!



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[GlennUK]

What you need is a extra column with a string of just the day/month part of the date.

Just put a formula like :
=TEXT(DAY(A1),"00&quot&"/"&TEXT(MONTH(A1),"00&quot

replacing A1 with the appropriate cell reference and copying the formula down as far as necessary.

You can then sort on this new column.


Cheers, Glenn.

 

[carrr]

Skip...you know, I always overlook PT's, don't know why.
I love it, user hates it. Still, a good fix.

GlennUK,
Can't make yours work...what am I overlooking? Data > Sort, when applied to the result column has no effect(???)

Anyway....resolution time. It turns out that this data ORIGINALLY comes out of a mainframe app as a text file. There's no need for it have ever been a date in the first place, it just came to me in that form. How's that for an easy fix?

 

[bluedragon2]

carrr,

What I did was create two columns by the date

in col B put =month(a1) and copied down

in col C put =day(a1) and copied down

then did a sort on col A,B, and C and did col B as first sort and col C as second sort.



Blue

If I wasn't Blue, I would just be a Dragon...

 

[GlennUK]

Ahh,

if I'd done Month then Day in the string the sort would've worked, as in ...

=TEXT(MONTH(A1),"00&quot&"/"&TEXT(DAY(A1),"00&quot

Sorry. Glad you found an easy fix.

Cheers, Glenn.

 

[carrr]

Glenn,

Doh! Didn't think to switch it myself. Nice touch.

Blue,
Another good one to remember...and, believe it or not, probably the most palatable of the three for the user in question.

Thanks guys...

 

[SkipVought]

he, he, he --

almost ALWAYS, more than one way to skin a cat!

poor cat! :-(



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[bluedragon2]

That I would believe since I am just a glorified user myself



Blue

If I wasn't Blue, I would just be a Dragon...

 

[tav1035]

carrr,
I would be careful from stripping away the year if at all possible, you might need it as a quick reference someday.
Try this->
(1) Highlight your date column and format cells.
(2) Go down to Custom and on the right side type mmmm and click ok.
(3) Go to Data->Sort (pick your column to sort).
(4) Choose Options at the bottom left
(5) Choose the drop down and pick January, February, March, etc.
(6) Click ok, and ok.
Now when you want to sort, just use your Sort Ascending hotkey.

This way you never have to destroy your data.

tav