convert 10113 to date format 1/1/2013

firstWeMeet

Board Regular
Joined
Feb 8, 2011
Messages
200
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.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Useful

Active Member
Joined
Mar 16, 2011
Messages
494
Hi,
try

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

Useful

Active Member
Joined
Mar 16, 2011
Messages
494
Hi,
it works as required check your date format settings if like this "dd.mm.yyyy" (from ctrl+! and custom)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,493
Messages
5,548,370
Members
410,828
Latest member
A9Bosv3
Top