Well, it’s not really that fun. Sometimes I need to manipulate data directly in Excel (or a SharePoint list) and don’t necessarily have the luxury of using additional columns for holding intermediate step values.
For example, a worksheet (or SPS list) has a column that “looks” like a date, “4/1/2018” (US formatting, M/D/YYYY), but the data isn’t treated as such in Excel. It’s literal text and things like sorting and other Date/Time functions, like DATEVALUE(), just fail.
So, time to hack on a “text to date” formula. It isn’t going to be nice since it has to do everything all at once, but for readability I’ll break it down.
First off, I’m going to use the “DATE” function to do the final string->date conversion. This takes the form DATE(year, month, day). Now to figure out each of those components!!!
Let’s assume the date string to convert is in A2 and currently formatted M/D/YYYY. The easy part is getting the year. It’s just the right 4 characters. Simple!
"Year: " & RIGHT(A2, 4)
Next is the month. That shouldn’t be too difficult. It’s gotta be some sort of LEFT string function, right? Yeah, the form for the function is LEFT(text, [num_chars]). Months can be either single (1-9) or double (10-12) digit numbers, so can’t hard-code [num_chars]. But, we can take advantage of the consistent format and use of delimiters, namely “/”. Using FIND(find_text, within_text, [start_num]) we can determine what position the first “/” is at and do a little math to get the actual character length we need.
"Month: " & LEFT(A2,FIND("/",A2)-1)
Heck, this hasn’t been too bad, so what’s the big deal with getting the day? Well, it’s in the middle of the string and as noted above, the month can be 1 or 2 digits and the day can be 1 or 2 digits (1-9 and 10-31 respectively), so again, no hard-coding values. Not many choices here, either MID(text, start_num, num_chars) or some weird combination of LEFT(text, [num_chars]) and RIGHT(text, [num_chars]). For simplicity, go with MID. This is primarily because we can easily get the start_num value of the first char after the first “/”, namely FIND(“/”,A2)+1). num_chars is where the pain is, but FIND will come to the rescue (along with a bit of math)! Basically, num_chars is the total length of the string minus the length of “month/” (actually the index of “/”) minus the length of “/year”.
"Day: " & MID(A2,FIND("/",A2)+1,LEN(A2)-FIND("/",A2)-5)
Do all the appropriate replacements in the DATE function and voila, one single ugly but useful function.
There is a presumption regarding the length of the year. What if it’s 2 digits, (e.g., 4/1/18)?
Not really a big deal (sorta, more on that later). In the process of accommodating for both 2 and 4 digit years, we can also get rid of any “constants” except to shift left/right from a delimiter. The new and improved “day” function: moves 1 char to the right of the first “/” and stops at 1 char to the left of the second “/”.
"Day: " & MID(A2,FIND("/",A2)+1,FIND("/",A2,4)-FIND("/",A2)-1)
Since this adjusts for both “year” formats, we should probably take a similar approach to getting the actual “year” value. Just a matter of taking all the characters to the right of the 2nd “/”.
"Year: " & RIGHT(A2,LEN(A2)-FIND("/",A2,4))
The one caveat here is that some applications (e.g., Excel) will presume that when working with a 2-digit year, it is at the beginning of the “known IT universe” which starts at 00:00:01, Jan 1, 1900, so in the example provided (4/1/18), the year “18” would be converted to 1918. Depending on context and data set, this may be easy to work with; just prepend the calculated value with “19” or “20”. If spanning both 20th and 21st centuries. well, good luck 🙂