DATE formatting in EXCEL

[nwc628]

I need to convert text inputted in the YYMM format (i.e. 1408= August, 2014) to Date format of MMM-YY (AUG-14)... I cannot for the life of me figure out how to do this. It gives me random dates... I also want to set it up so whoever enters it as 1408 in the future, will have it automatically converted to AUG-14.

 

[VoG]

Try

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

Format as required.

 

[nwc628]

Well that worked for the formatting part, but I just want one column. When I try to move it to replace the date column, I get a value error

 

[nwc628]

is there a way to have it format inputted text yymm to date mmmyy automatically? (e.g. someone types 1408 in cell and it automatically converts to AUG-14). It just seems so simple and it's been irritating me for hours

 

[rob.barnes01]

Set up data validation in this column requiring users to input the date correctly.