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.
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.
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.