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

`=RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4)`

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.

There is evidently a bundle to realize about this. I suppose you made various good points in features also.

Thanks for sharing, this is a fantastic blog.Really looking forward to read more. Will read on

Fantastic blog post.Much thanks again. Great.

Why people still use to read news papers when in this technological globe all is accessible on web?

Be grateful you for spending time to speak about this, I think strongly about that and delight in reading read more about this topic. Whenever possible, just like you become expertise, do you mind updating your web site with a lot more details? It can be highly great for me. Two thumb up in this article!

If some one desires to be updated with newest technologies after

that he must be pay a visit this site and be up to

date everyday.

Your style is very unique compared to other people I ave read stuff from. Many thanks for posting when you have the opportunity, Guess I will just book mark this site.

Useful information. Fortunate me I discovered your web site unintentionally, and I’m surprised why this

accident didn’t happened earlier! I bookmarked it.

This really answered my drawback, thank you!

It as going to be finish of mine day, but before end I am reading this fantastic article to increase my experience.

Thank you for your blog.Really looking forward to read more. Great.