change 20071201 to date
 

Hello. My little problem is that I want to change this date format: 20071201
to 2007-12-01, because excels doesn't recognize 20071201 as a date. I'm
Dutch. I know the Dutch solution to the problem. It's:
=DATUMWAARDE(RECHTS(A1;2)&"-"&DEEL(A1;5;2)&"-"&LINKS(A1;4))
However, I cannot make the translation work on an English version of excel.
(...=DATEVALUE...?)
Can anyone help me out? Thanks in advance.

change 20071201 to date
 

Maybe try Data Text to columns ?

Select the "dates" col
Click Data Text to columns
Click Next Next
In step 3 of the wiz,
Check "Date", then select: YMD from the droplist
Click Finish
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Stampertje" wrote:
Hello. My little problem is that I want to change this date format: 20071201
to 2007-12-01, because excels doesn't recognize 20071201 as a date. I'm
Dutch. I know the Dutch solution to the problem. It's:
=DATUMWAARDE(RECHTS(A1;2)&"-"&DEEL(A1;5;2)&"-"&LINKS(A1;4))
However, I cannot make the translation work on an English version of excel.
(...=DATEVALUE...?)
Can anyone help me out? Thanks in advance.

change 20071201 to date
 

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
and custom format the result cell like "yyyy-mm-dd"!

Regards,
Stefi

€žStampertje€� ezt Ã*rta:

Hello. My little problem is that I want to change this date format: 20071201
to 2007-12-01, because excels doesn't recognize 20071201 as a date. I'm
Dutch. I know the Dutch solution to the problem. It's:
=DATUMWAARDE(RECHTS(A1;2)&"-"&DEEL(A1;5;2)&"-"&LINKS(A1;4))
However, I cannot make the translation work on an English version of excel.
(...=DATEVALUE...?)
Can anyone help me out? Thanks in advance.