Excel MMYY format in order [Solved/Closed]

nickkyh 3 Posts Monday February 24, 2014Registration date February 24, 2014 Last seen - Feb 24, 2014 at 11:36 AM - Latest reply: nickkyh 3 Posts Monday February 24, 2014Registration date February 24, 2014 Last seen
- Feb 24, 2014 at 05:54 PM
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
See more 

5 replies

Best answer
ac3mark 9942 Posts Monday June 3, 2013Registration dateModeratorStatus July 17, 2018 Last seen - Feb 24, 2014 at 02:19 PM
1
Thank you
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

Thank you, ac3mark 1

Something to say? Add comment

CCM has helped 1699 users this month

ac3mark 9942 Posts Monday June 3, 2013Registration dateModeratorStatus July 17, 2018 Last seen - Feb 24, 2014 at 11:58 AM
0
Thank you
OK, hang in there. Check this out:


nickkyh 3 Posts Monday February 24, 2014Registration date February 24, 2014 Last seen - Feb 24, 2014 at 12:41 PM
0
Thank you
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
ac3mark 9942 Posts Monday June 3, 2013Registration dateModeratorStatus July 17, 2018 Last seen - Feb 24, 2014 at 01:37 PM
0
Thank you
yea. I messed that all up! you are welcome. Ill finish my other stuff and get back to this.
nickkyh 3 Posts Monday February 24, 2014Registration date February 24, 2014 Last seen - Feb 24, 2014 at 05:54 PM
0
Thank you
That does the trick, thank you ac3mark, you are a gent and a scholar!

Dates sorted and problem solved:)