Excel help a small issue

Status
Not open for further replies.

John4321

Forerunner
Is there a way I can convert this date 20131218 in excel to show as year date and month format. I have excel 2007. So I want it show accurately with slash bars in between or like 18th DEC 2013 and as such like that
 
You can use the text to columns wizard and change the format to date (YMD) and then make it appear as you wish.
 
Just right click on the cell and click format cell, then go to date and select the desired format you wish to use. There is also custom tab there to use your specific format.
 
If you know for sure that the date is always in YYYYMMDD such as 20160516 instead of 2106516 then it is very simple. Just put this in the cell that you want assuming A1 is where you have 20131218.

=DATE(LEFT(TEXT(A1,0),4), RIGHT(LEFT(TEXT(A1,0),6),2), RIGHT(TEXT(A1,0),2))

There could simpler methods but this will teach you the use of the Date, Left, Right and Text functions too.
 
Status
Not open for further replies.