I was just asked by my colleague how to turn 20080101 (in a file provided by a client) to 01/01/2008 (to be used in our system) in Excel where the cell is presented as text. We can easily recognise the date, but computers are not so intelligent to put up with these variations of how we operate.
There are a number of ways, but mine relies on certain assumptions, that numbers below 10 are pre-padded with a zero (0), ie. the 9th is represented as 09. This method uses the VB functions in Excel, but for simpilicity's sake I will break this down into stages.
For this example, I will use the above date (20080101) which is in cell A1.
Firstly, we need to split the date in and use cells A4, B4, and C4:
So we need to use a formula in A4 to get the year, B4 to get the month and C4 to get the day:
We have now effectively broken down the date into usable chunks, so you can do anything with the formulas. However in my situation I need to present the date differently by using the CONCATENATE function.
This is achieved with the following formula in cell A8:
And there you have it.
There are a number of ways, but mine relies on certain assumptions, that numbers below 10 are pre-padded with a zero (0), ie. the 9th is represented as 09. This method uses the VB functions in Excel, but for simpilicity's sake I will break this down into stages.
For this example, I will use the above date (20080101) which is in cell A1.
Firstly, we need to split the date in and use cells A4, B4, and C4:
So we need to use a formula in A4 to get the year, B4 to get the month and C4 to get the day:
- Year
=LEFT(A1,4) - Month
=MID(A1,5,2) - Day
= RIGHT(A1,2)
We have now effectively broken down the date into usable chunks, so you can do anything with the formulas. However in my situation I need to present the date differently by using the CONCATENATE function.
This is achieved with the following formula in cell A8:
=CONCATENATE(C4, "/", B4, "/", A4) And there you have it.
Comments