Formatting using DATEDIF and EXTEXT

[john8714]

I am curious if it is possible to transform a six digit number with EXTEXT and DATEDIF into a birthday notation w/o using the cell formating?

Example: 870601 --> 1987-06-01

 

[azumi]

Perhaps

=LEFT(A1,2)+0+IF(LEFT(A1,2)+0>19,1900,2000)&"-"&MID(A1,3,2)&"-"&RIGHT(A1,2)

 

[john8714]

thank you.

 

[bosco_yip]

Maybe,

1] Assume your data located in A1

2] Select A1 >> Text to Columns >> Delimited >> Next >> Next >> In step 3 of 3, click "Date", and choose " YMD" >> Finish.




Regards
Bosco

 

[pecoflyer]

What is EXTEXT?

 

[Chihiro]

I assume different language version of Excel. Swedish?
https://support.office.com/sv-se/ar...-EXTEXTB-d5f9e25c-d7d6-472e-b568-4ecb12433028

Edit: I think it's MID, but the example in link above confuses me... how do you get result that's not in the data??


Edit2: Huh, I guess it's direct translation of en-us sample. "Fluid Flow" and result was translated as well...

 

[Nikunj Rastogi]

Hi Basco, Text 2 Column is not work here .

 

[Nikunj Rastogi]

Hi John , please confirm that do you have your data in correct form because if the number will like this 143211 in which 32 does not belongs to any date for month.

 

[John Jairo V]

Hi, to all!

This could be an option:
=DATEVALUE(TEXT(A1,19+(A1<200000)&"00-00-00"))
Or:
=--TEXT(A1,19+(A1<200000)&"00-00-00")

Blessings!

 

[pecoflyer]

I assume different language version of Excel. Swedish?
https://support.office.com/sv-se/ar...-EXTEXTB-d5f9e25c-d7d6-472e-b568-4ecb12433028

Edit: I think it's MID, but the example in link above confuses me... how do you get result that's not in the data??
View attachment 47857

Edit2: Huh, I guess it's direct translation of en-us sample. "Fluid Flow" and result was translated as well...

Thanks for looking it up. I didn't think of it