Fresh Excel Tips

Previously, we looked at how you can use the LEFT, MID, and RIGHT functions to extract parts of a date, you can see it HERE. For this example, we will be using the TEXT Function in combination with the DATE Function to turn values that Excel doesn’t recognize as being dates into recognizable date values. As in the previous example, the text has to be extracted and specially formatted as shown in our screenshot below.

How to Extract parts of a date using the TEXT and DATE Functions

1. Enter your dates as shown below or use your own data.
2. Select cell B2 and type the following formula: =TEXT(DATE(RIGHT(A2,4),MID(A2,3,2), MID(A2,1,2)),”YYYY-MM-DD”).
3. Use the fill handle to copy the formula to the remaining cells in Column B as necessary.

You should now be set! This should work in Microsoft Excel 97, 2000, 2003 and 2007.

1. Use the LEFT, MID, and RIGHT functions to extract date parts
2. How to Combine Parts of Date using the DATE Function in Excel
3. How to Use the YEAR function to extract the year part of a date
4. Combine Cells with text, date, time and numbers in Excel
5. How to Extract digits from a cell using the MID Function in Excel