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:
| String | Year | Month | Day | Date |
|---|---|---|---|---|
| 5/31/2016 | 2016 | 5 | 31 | 31/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.
