Converting Dates in Excel 2010

So, you have dates that are formatted M/D/YYYY (month/day/year) and Excel thinks they're text? There MAY be a better way to do this but Excel 2010 help is seriously dysfunctional so I came up with this.

So, a quick formula to convert them to values is:

=DATE(RIGHT(B2,4),LEFT(B2,FIND("/",B2)-1),MID(B2,FIND("/",B2)+1,FIND("/",B2,FIND("/",B2)+1)-(FIND("/",B2)+1)))

=DATE( 

YYYY >   RIGHT(B2,4),

MM >     LEFT(B2,FIND("/",B2)-1),

DD >     MID(B2,FIND("/",B2)+1,

             FIND("/",B2,FIND("/",B2)+1)-(FIND("/",B2)+1)

         )

)

The Year: The date (i.e. "1/24/1945") is recognised not as a date, but a text value. To get the year we grab the rightmost 4 digits and capture that as the first parameter in the DATE() function, 1945. That was easy.

The Month: This is nearly as easy, but we use FIND() to determine where the first slash is we use the FIND() function, including the parameter "/" (slash) and the source cell (B2), we subtract one (1) because we don't want the slash included in what we're enumerating. Then we use LEFT() to take what we need from the beginning (left-side) of the text string. as per the above date example, the month would be 1.

To get the day we use MID() because we need to grab a MIDdle segment of the string, we will use FIND() recursively to determine where to start the text segment, then again to find the second slash (starting after the first slash) then determine how many characters to capture by finding the second slash, then subtracting the two positions. Using the example above the day would be 24.

** Source cell in this example is B2