Dates converted into text


R

rjagathe

I receive Excel data from my branch offices.the data should contain
date coloumns.But some cells contain dates in "<year><month.><date>"
format i.e.,21/02/2010 in dd/mm/yyyy format is displayed as
20100221.Why it is happening?How to convert them into dd/mm/yyyy
format?
I tried to record and run a macro to insert "/" between year and month
and between month and date,then clicking "Enter" button...But,it
displays same date in all the cells in which I run the macro.
 
Ad

Advertisements

G

Gord Dibben

Try Data>Text to Columns>Next>Next>Column Data Format DMY


Gord Dibben MS Excel MVP
 
F

FSt1

hi
try a formula.
=MID(B6,5,2)&"/"&RIGHT(B6,2)&"/"&LEFT(B6,4)
worked for me.
put the formula in a blank formula and copy down.
then copy the helper column and paste special values.

regards
FSt1
 
R

rjagathe

hi
try a formula.
=MID(B6,5,2)&"/"&RIGHT(B6,2)&"/"&LEFT(B6,4)
worked for me.
put the formula in a blank formula and copy down.
then copy the helper column and paste special values.

regards
FSt1

hi
I put 19980427 in cell B1 and put your formula in A1.But A1 becomes
19980427 only.It does not return 27/04/1998.

regards
rjagathe
 
R

rjagathe

Try Data>Text to Columns>Next>Next>Column Data Format DMY

Gord Dibben  MS Excel MVP

Hi
when I tried this, for whatever year I gave, it returns year as 1905
only.

with regards
rjagathe
 
Ad

Advertisements

Ad

Advertisements


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

Top