Use the LEFT, MID, and RIGHT functions to extract date parts
For this example, we have values in column A that to us looks like dates but Excel cannot interpret them as dates. To resolve this problem, we will extract parts of the date using the LEFT, MID and RIGHT Functions and then we’ll put them in the correct format by using the DATE Function. Here is how we’ll achieve this.
How to extract and combine parts of a date
- Enter your dates as shown in the example in Column A or use your own.
- Select Cell B2 and type the following formula: =DATE(LEFT(A2,4),MID(A2,FIND(”.”,A2,1)+1,2), RIGHT(A2,2)).
- Use the fill handle to copy the formula as needed.
The first four digits have to be transferred with the LEFT function. Then use the FIND function to detect the
decimal point. On the right of the first decimal point (+1), two digits are interpreted as the month using the MID function. On the right side of the second decimal point, use the RIGHT function to extract two digits as the day value. This should work in Microsoft Excel 97, 2000, 2003 and 2007.
- How to use the TEXT function to extract parts of a date in Excel
- How to Combine Parts of Date using the DATE Function in Excel
- How to Extract digits from a cell using the MID Function in Excel
- How to Use the YEAR function to extract the year part of a date
- How to sort birthdays using the MONTH and DAY functions in Excel
