I have worked with data downloads from companies where the date comes out of the database it is in an unsable format for date purposes. Instead of displaying 30/05/2010 the cell would read 20100530. I found a workaround for this using three different functions in Excel all in one formula. The functions are LEFT, RIGHT and MID. And the formula will then concatenate these together to give the required date format.
So in my example I want to turn 20100530 into 30/05/2010. Let’s assume that the date is in cell A1 and the new version will be cell B1. In cell B1 type the following formula
Now lets break that down into it’s component parts.
RIGHT(A1,2) – This first part is telling Excel to start at the right hand side of our date number and then only take the first two numbers from that right hand side. Using RIGHT(A1,2) we give us 30.
&"/"& – This tells Excel to insert a forward slash. It is concatenated using the ampersand character, &. Whenever you insert something like this, you must have an ampersand before and after the string you want to insert. And the string must be inside speech marks “/” like so. The true is same for the other use of the forward slash. This part of the formula adds a / to 30, so we now have 30/.
MID(A1,5,2) – For this part I am telling Excel to start in the middle of the date number. I have specified where to start in the string and how many characters to take by typing 5,2 after the A1 cell reference. When using the MID function Excel always starts from the left. So 5 places in from the left from 20100530 would start at the third zero. Then a comma and the 2 specifies how many characters to take starting from the fifth position. This adds the month to our date, so we now have 30/05.
&"/"& – This is second use of the forward slash. This part of the formula adds a / to 30/05, so we now have 30/05/.
LEFT(A1,4) – The final part of the this formula concatenates the year to our nearly completed date. Using the LEFT function tells Excel to start from the left, and then after the comma simply specifiy how many character to take. In our case its 4 characters which represents the 2010 we need to complete the date. So we now have a finished date in UK format now displaying 30/05/2010. Finally copy the cell and then do paste special and click values. This removes the formula and replaces it with the value of our now worked out date.