convert a no to date (dd/mm/yyyy)

[Gohar Shahab]

I have following imported nos which i want to convert into dates on format dd/mm/yyy

Book6
	A	B	C	D
1	91270
2	20775
3	140700
4	30902
5	30254
6	40364
Sheet1

 

[SteveO59L]

Format the column/cells as date

 

[jasonb75]

Try

=DATE(RIGHT(A1,2),MID(A1,LEN(A1)-3,2),LEFT(A1,LEN(A1)-4))

Might be giving wrong results for some cells.

 

[Gohar Shahab]

i get following erros highlighted in yellow

Book7
	A	B	C	D
1	91270	09/12/1970
2	20775	02/07/1975
3	140700	14/07/1900
4	30902	03/09/1902
5	30254	03/02/1954
6	40364	04/03/1964
Sheet1

 

[barry houdini]

Assuming your default date setting is mm/dd/yy try

=TEXT(A1,"00-00-00")+0

format as date

 

[Ron Coderre]

Try this:

=--TEXT(A1,"00\/00\/00")

Format the cell as a date

or...perhaps you could use Text-to-columns to convert the
numbers to dates. (not sure where that is located in XL2007)
Set the column to the appropriate DMY or MDY configuration.


Does that help?

 

[Gohar Shahab]

thanks everyone,,,it worked...