Numeric Date to proper date format

[Ducttape19]

Hey all, I have a workseet that contains a row of numbers that represent dates. The data is formated like this 20040201 or YYYYMMDD all as numbers. I would like to be able to convert it into a real date like March 1st, 2004 or even Mar-01-2004 or something more readable than it is now. I was thinking of using the DATE(day,month,year) function but before I did that I would have to split up my number into 3 columns. is there a way to take my big date number and put the first 4 numbers in column B then the 5th and 6th numbers and put them in column C and then the 7th and 8th numbers in column D? I have tried changing the format in the cell properties and I have also tried using the datevalue() function. I am sure that this is possible somehow but I can't figure it out. Or if you have a different sugestion how I can format the date properly let me know. Thanks for you time.

 

[Joe4]

Highlight the column. Under the Data drop down menu, select Text to Columns. Hit Next twice. Select "Date" as your format, and select the "YMD" date option and click finish. This will convert all those values to dates.

 

[BuddieB]

Highlight the data range.
Use DATA - TEXT TO COLUMNS - Fixed Width and then set the Date Option to YMD

this will conver the value to an actual date value.

 

[PaddyD]

"but before I did that I would have to split up my number into 3 columns..."

no you wouldn't:

date(left(a1,4),mid(a1,5,2),right(a1,2))

 

[Cbrine]

Try this

=DATEVALUE(LEFT(A1,4)&"-"&MID(A1,5,2)&"-"&RIGHT(A1,2))

You may need to reverse the Mid and right commands depending on where you day and month appear.

 

[Ducttape19]

Thanks alot for your help I will have to remember the RIGHT() LEFT() and MID() functions those will come in handy I am sure. Thanks

 

[Joe4]

Familiarize yourself with Text to Columns as well. It is a great tool for converting data into another format very quickly and easily (i.e. in addition to this dates issue, if you have values entered as text, you can use this to convert them to numbers). It requires no inserted columns or formulas.

If you work a lot with data, you will find it very useful.