In previous posts I’ve been going through some formulas designed to extract and convert a text string Date, to a Date serial number.
- Extract a Date from Text in Excel
- The MID Function in Excel
- The RIGHT Function in Excel
- CONCATENATE Function or Ampersand Operator in Excel
- The TYPE Function in Excel
- The VALUE Function in Excel – Converting Text Dates
Given the text string Date below in cell A2, what I normally do is enter a single formula, then change the cell formatting.
The formula above in cell B2 is:
= – - (MID(A2,5,6) & “, ” & RIGHT(A2,4))
Which leaves a numeric date serial number.
I then change the formatting by using the keyboard shortcut Ctrl+1 to bring up the Format Cells dialog box, click the Number tab, select Date (leaving the default short date format), and then click OK.
Oh, and then copy the formula down by selecting cell B2 and double clicking the fill handle.
There are a number of things in this formula: double negation, MID Function, Ampersand Operator, and RIGHT Function that combine easily, once you know how the individual parts work.