number to date format

  • Thread starter Thread starter womit
  • Start date Start date
W

womit

how do convert a cell with 8 numbers 19680422 into the date format 4/22/1968 ?
 
Replace cell A1 in the expample with the corresponding cell in your worksheet:

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
 
Assuming they start in cell A1, put this in B1:

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

Format the cell how you want it to appear, then copy down to B8.

Hope this helps.

Pete
 
Another one:
=--(TEXT(A1,"0000\/00\/00"))
and format it as a date.

If you have a single column of these things, you can:
select the range
data|text to columns (xl2003 menus)
fixed width (no separators!)
and choose ymd as the date format

And then format it the way you like.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Which is the best MBOX Converter Tool different formats conversion? 4
Need to open eer file without converting 8-7440 to a date? 1
Replace numbers with time 3
Delete cells 10
Data being corrupted by excels automatic date recognition re-forma 6
Format date in a cell 2
Converting dd:hh:mm to [h]:mm not working. 3
Adding Text, Date and Numbers together in a formula 2

Back
Top