Numeric Date to proper date format

Ducttape19

New Member
Joined
Aug 28, 2002
Messages
46
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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,755
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Aug 6, 2003
Messages
174
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
"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

Well-known Member
Joined
Dec 2, 2003
Messages
3,196

ADVERTISEMENT

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

New Member
Joined
Aug 28, 2002
Messages
46
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,755
Office Version
  1. 365
Platform
  1. Windows
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.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,561
Messages
5,765,113
Members
425,261
Latest member
JW00

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top