How to convert any number series in date format ?

Converting year/month/date format in date/month/year or other format using excel formulas .

Actually i was facing problem regarding the format of dates. The spreadsheet that i downloaded from my stock broker was having different format from what i wanted to have, for income tax filing purpose.  The data was in following manner and i had tried number format option multiple times and it was not working at all.

BuyDate	SellDate
20161125	20161125
20160708	20160708
20160627	20160627
20160704	20160704
20160630	20160630
20160711	20160711
20160708	20160708
20160707	20160707
20160630	20160630
20170110	20170110

So here is what i did.

 

First use the replace number to put a forward slash in between the numbers.

 

In a blank cell which besides your phone numbers to enter this formula: =REPLACE(REPLACE(A2,4,0,”/”),8,0,”/”),

This will turn the number in new and better format.

2016/11/25

see screenshot:

Now we have to change and reverse the date. In the format that we want to have. Select a blank cell next to your date, for instance. Type this formula =TEXT(G1, “dd-mm-yyyy”), and press Enter key, then drag AutoFill handle over the cells needed this formula.

 

 

Now the last thing. If you want to save all of your hard work and keep the values safe try this.

How to save this file so that your hard work doesn’t go wasted. Copy all the cells that you have successfully converted. Simply by pressing CTRL + C

Press Ctrl+Alt+V. This displays the Paste Special dialog box.

Or simply press CTRL+V and then from paste options choose values.