Convert a Text String Date to Date Serial Number

In previous posts I’ve been going through some formulas designed to extract and convert a text string Date, to a Date serial number.

Given the text string Date below in cell A2, what I normally do is enter a single formula, then change the cell formatting.

Convert Text Date to Serial Date Formula Unformatted

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.

Format Cells Dialog Box

Oh, and then copy the formula down by selecting cell B2 and double clicking the fill handle.

Convert Text Date to Serial Date Formula

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.