convert 10113 to date format 1/1/2013

[firstWeMeet]

Hi, can someone help me to convert date format from 10113 to 1/1/2013.
note that the data is from 10113 to 120113 (1 year date). format cell is not working here.

 

[Useful]

Hi,
try

=DATE(MOD(A1,100)+2000,INT(REPLACE(A1,1,LEN(INT(A1/10000)),"")/100),INT(A1/10000))

 

[firstWeMeet]

Hi,
try

=DATE(MOD(A1,100)+2000,INT(REPLACE(A1,1,LEN(INT(A1/10000)),"")/100),INT(A1/10000))

Thanks, but when the opposite cell contain like 100113 it showing 1/10/2013 instead of 10/1/2013.

 

[Useful]

Hi,
it works as required check your date format settings if like this "dd.mm.yyyy" (from ctrl+! and custom)

 

[mfexcel]

For your reference.
Convert an 8-digit number into Excel-recognizable Date | wmfexcel

If you follow the logic, you should be able to solve your problem.