31 May 2016

Formula to Convert mm/dd/yyyy String to Date

Some data I receive has the date in a string in mm/dd/yyyy format, which is easier to sort or filter in Excel when converted to a date number. If the data is in cell A2 then the formulas for splitting the date string into substrings and creating a date value are:

StringYearMonthDayDate
5/31/2016201653131/05/2016
  • Year: =RIGHT(A2,4). The year is always the last four digits.
  • Month: =LEFT(A2,FIND("/",A2)-1). The month is the one or two digits before a forward slash.
  • Day: =MID(A2,FIND("/",A2)+1,FIND("/",A2,4)-FIND("/",A2)-1). The day is the digits between the first and second forward slash.
  • Date: =DATE(B2,C2,D2).

Probably the non-obvious bit is finding the second forward slash. The FIND() formula takes three arguments: find_text, within_text, start_num, where start_num is the position to start the search for find_text. Since the month and day numbers are always one or two digits, the second forward slash must be in position 4 ("m/d/yyyy"), 5 ("mm/d/yyyy", "m/dd/yyyy") or 6 ("mm/dd/yyyy"), so starting the search from position 4 will always find the position of the second forward slash.

Later ... I could just use Data, Text To Columns and split the date string into three columns using the forward slash as the delimiter.