Converting date as text to a date format

[DashboardNovice]

I have a text value in the form of 5122017 which I want to convert to 05/12/2017. I tried the TEXT( ) but that appears to give you the date only if you apply it to the date's corresponding serial number. I also tried the DATEVALUE( ) and that didn't work either.

Is there a function that can make this conversion.

 

[SirJB7]

Hi, DashboardNovice!

I'm using a DMY date format configuration, but I think it'll work too for MDY:
=FECHANUMERO(IZQUIERDA(SI(RESIDUO(LARGO(A1);2)=0;A1;"0"&A1);2)&"/"&EXTRAE(SI(RESIDUO(LARGO(A1);2)=0;A1;"0"&A1);3;2)&"/"&DERECHA(SI(RESIDUO(LARGO(A1);2)=0;A1;"0"&A1);4)) -----> in English:
=DATEVALUE(LEFT(IF(MOD(LEN(A1),2)=0,A1,"0"&A1),2)&"/"&MID(IF(MOD(LEN(A1),2)=0,A1,"0"&A1),3,2)&"/"&RIGHT(IF(MOD(LEN(A1),2)=0,A1,"0"&A1),4))

Regards!

 

[vletm]

DashboardNovice
The challenges are number of days and months!
if 'day' is over '9' or 'month' < 10 then some parameters have to change!
=DATEVALUE(RIGHT(B2,4)&"/"&MID(B2,2,2)&"/"&LEFT(B2,1))

 

[p45cal]

In your example of 5122017, you use 7 digits. If the seven digits were:
2112017, would that be 2-Nov-2017 or 21-Jan-2017 (or perhaps 11-Feb-2017)?

 

[vletm]

DashboardNovice
I noticed below after my previous shut down:

If You want to use 'DATEVALUE' then just choose correct syntax
... after You'll know wanted result.