Thread: Four Digits into a Date

I get date information in the format mmyy (eg. 0311 is March, 2011). Is there any way to either make the information yymm or change it into some other Excel recognizable format that can be displayed (eg. Mar-2011, etc.)?

I'm trying to conditionally format the cells so that certain dates receive different background colors, depending on how far in the future they are. In the current format, I can't seem to make Excel realize which range I want to color.

Thanks!

hi,

you can format your cells to show them in the order that you want.
Right click then format cells
On custom, enter yymm as you suggested

hope that helps

Hi,

How about

=DATE(RIGHT(A1,2),LEFT(A1,2),1)

formatted as mmm-yyyy

HTH

if your dates start in cell A2, you can put this in B2 and fill down:

=DATE(VALUE(20&RIGHT(A2,2)),VALUE(LEFT(A2,2)),1)

...however, this assumes that you don't have any years before 2000 and no years after 2099. After doing this, you can copy the new column, right click, Paste Special --> values --> OK to just keep the dates and not the formulas. Format in one of the Month-Year formats.

I believe Richard's suggestion will give Mar-1911. You could tweak it as follows to give Mar-2011

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

Hi ddll

Thanks for the correction - I hadn't spotted that. Which has actually identified what looks like a bug in Excel for Mac 2004.

There's a parameter setting whereby 2 digit year numbers less than the parameter digits are treated (or should be treated) as 21st century, whereas 2 digit numbers greater than the param are 20th C. e.g. with a parameter of 30, the system implies that =Date(11,1,1) will display 1st Jan 2011, whereas =date(31,1,1) will display 1st Jan 1931.

Is there a similar setting in Excel for Windows?

Rgds

Originally Posted by Richard Buttrey

There's a parameter setting whereby 2 digit year numbers less than the parameter digits are treated (or should be treated) as 21st century, whereas 2 digit numbers greater than the param are 20th C. e.g. with a parameter of 30, the system implies that =Date(11,1,1) will display 1st Jan 2011, whereas =date(31,1,1) will display 1st Jan 1931.

It doesn't work that way for DATE function, Richard. If you use DATE(11,1,1) then you'll always get 1911. If the year is a number less than 1900 then it's always assumed that the year is that number + 1900 so both of these give the same date

=DATE(2011,1,1)

=DATE(111,1,1)

The parameter to which you refer only applies to dates typed like 1/1/11, which would be interpreted as 2011, or 1/1/31 which would be interpreted as 1931

Excellent ddll. Thanks for that clarification. You live and learn as they say....

Rgds

Try using this formula which will work even if the leading zero is the result of formatting

=DATE(RIGHT(A1,2)+100,LEFT(TEXT(A1,"0000"),2),1)

custom format result cell as mmm-yyyy

Originally Posted by dogfood411

OK, you guys are awesome.

OK, I have tried most of the suggestions and here are some of the results:
0311 (should be Mar-2011): Jul-2013
1210 (should be Dec-2010): Dec-2010 (Huzzah!)
0611 (should be Jun-2011): Jan-2016
0910 (should be Aug-2010): Jul-2017

Hi,
With the above 0311,1210 etc in A1:A4 then the formula below, which I think has been suggested before, produces the results you expect. Not quite sure why you're getting July 2013 for instance

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

Rgds

Fantastic!

I used DDL's suggestion and the angels started singing.

Thanks, all!