change 20071201 to date

[Stampertje]

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.

 

[Max]

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

 

[Stefi]

=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: