Excel MMYY format in order [Solved/Closed]

Report
Posts
3
Registration date
Monday February 24, 2014
Status
Member
Last seen
February 24, 2014
-
Posts
3
Registration date
Monday February 24, 2014
Status
Member
Last seen
February 24, 2014
-
Hello,

I have a data set and a column currently with a selection of dates in MYY and MMYY, eg.

909 (Sep 2009)
1011 (Oct 2011)
1212 (Dec 2012)
113 (Jan 2013)
213 (Feb 2013)
etc..

I have an issue with sorting in date order because the number is recognised by the first digit so all Januarys appear first regardless of year. Can someone help so that I can format the date in a uniform way so I can arrange my data in chronological order!

Many thanks for looking:)
Nickkyh

5 replies


Ok, let us try this:

=DATE(RIGHT(A1,LEN(A1)-2)+100,LEFT(A1,LEN(A1)-2),1)

This assumes always 3 or 4 digit dates.

let me know!

"If you can't soar with the eagles, then don't fly with the flock!" - Oliver Sykes; Bring Me The Horizon
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month


OK, hang in there. Check this out:


Posts
3
Registration date
Monday February 24, 2014
Status
Member
Last seen
February 24, 2014
1
Hey ac3mark,


Good shout and I have tried similar methods but if you try putting 909 for instance, I know its Sept 2009 but excel has other ideas, so =TEXT(A1,"YY-MM") for instance is apparently 02-06!

As I have a large amount of recurring data, manually changing it would not be ideal and seems no way of formatting the date when it is in that number form that I have tried so far.

Many thanks
nickkyh

yea. I messed that all up! you are welcome. Ill finish my other stuff and get back to this.
Posts
3
Registration date
Monday February 24, 2014
Status
Member
Last seen
February 24, 2014
1
That does the trick, thank you ac3mark, you are a gent and a scholar!

Dates sorted and problem solved:)