Making a number cell into a date

[burnsa9]

I am trying to take a date that looks like 20121222 into a date that looks like 2012-12-22. I already tried changing the format to a date format, but that didn't work. Any advice is greatly appreciated!

 

[Norie]

Various ways to convert that to a date.

If you have a column of values like that you could select the column, goto Data>Text to columns... and check YMD as the column data format on the 3rd step.

If you want a formula here's one.

=TEXT(A1,"0000\/00\/00")+0

 

[Rick Rothstein]

I am trying to take a date that looks like 20121222 into a date that looks like 2012-12-22. I already tried changing the format to a date format, but that didn't work. Any advice is greatly appreciated!

I assume you are trying to change those numbers into real Excel dates right in the cells they are in. If so, select the cells with those dates in them, then select the "Data" tab on the Ribbon and click the "Text to Columns" button located in the "Data Tools" panel... when the dialog box appears, click the "Next" button twice (you'll end up on the "Step 3 of 3" dialog page) and then select the "Date" option button and choose "YMD" from its drop down list... click the "Finish" button and your numbers will be real Excel dates. Now you can Custom Format the cells using this Type pattern format...

yyyy-mm-dd


EDIT NOTE: I see Norie posted the same thing I did, but in an abbreviated form. I decided to leave my post up just in case the OP (or a future reader of this thread) is not familiar with the "Text to Columns" dialog and, perhaps, needs the step-by-step instructions that I posted.