# Numeric Date to proper date format

#### Ducttape19

##### New Member
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.

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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.

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.

"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))

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.

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

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.

Replies
21
Views
659
Replies
4
Views
225
Replies
2
Views
161
Replies
12
Views
457
Replies
2
Views
106

1,203,534
Messages
6,055,947
Members
444,839
Latest member
laurajames

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back