Parse date in any text format
Syntax: =\PARSEDATE(date_text, format) => date
=LAMBDA(text,format,
LET(
pattern, TEXT(32204.67022,format),
switchsearch, LAMBDA(x,INDEX(TEXTSPLIT("02,03,1988,16,05,07,2,3,88,04,4,5,7",,","),x)),
substNonDigits, LAMBDA(t,
TEXTSPLIT(
REDUCE("",SEQUENCE(LEN(t)),
LAMBDA(acc,val,
IF(NOT(ISERROR(FIND(MID(t,val,1),"0123456789"))),
acc&MID(t,val,1),
acc&"."
)
)
)
,,"."
)
),
pat_arr, substNonDigits(pattern),
text_arr, substNonDigits(text),
findInPattern, LAMBDA(what,
REDUCE(0,SEQUENCE(ROWS(pat_arr)),
LAMBDA(acc,val,
IF(NOT(ISERROR(FIND(what,INDEX(pat_arr,val))))
,val
,acc
)
)
)
),
index_arr, MAKEARRAY(13,1,LAMBDA(x,y,findInPattern(switchsearch(x)))),
pos_arr, MAKEARRAY(13,1,LAMBDA(x,y,FIND(switchsearch(x),INDEX(pat_arr,INDEX(index_arr,x))))),
is12h, NOT(ISERROR(FIND("4",pattern))),
isPM, IFERROR(FIND("p",LOWER(text)),10000)<IFERROR(FIND("a",LOWER(text)),10000),
getfromtext, LAMBDA(x,l,
MID(
INDEX(text_arr,INDEX(index_arr,x)),
INDEX(pos_arr,x),
l
)
),
d, IFS(
INDEX(index_arr,1)<>0,getfromtext(1,2),
INDEX(index_arr,7)<>0,getfromtext(7,2),
TRUE,1
),
m, IFS(
INDEX(index_arr,2)<>0, getfromtext(2,2),
INDEX(index_arr,8)<>0, getfromtext(8,2),
TRUE,1
),
y, IFS(
INDEX(index_arr,3)<>0,getfromtext(3,4),
INDEX(index_arr,9)<>0,LET(yy,getfromtext(9,2),IF(VALUE(yy)<=50,yy+2000,yy+1900)),
TRUE,1900
),
h, IF(is12h,
IFS(
INDEX(index_arr,10)<>0, MOD(getfromtext(10,2),12)+IF(isPM,12,0),
INDEX(index_arr,11)<>0,MOD(getfromtext(11,2),12)+IF(isPM,12,0)
),
IF(INDEX(index_arr,4)<>0,getfromtext(4,2),0)
),
mm,IFS(
INDEX(index_arr,5)<>0,getfromtext(5,2),
INDEX(index_arr,12)<>0,getfromtext(12,2),
TRUE,0
),
s, IFS(
INDEX(index_arr,6)<>0,getfromtext(6,2),
INDEX(index_arr,13)<>0,getfromtext(13,2),
TRUE,0
),
DATE(y,m,d) + TIME(h,mm,s)
)
)
Documentation
Parses dates formatted as text back into the Excel date format, given the text input and format specification.
Format is compatible with custom date numberformats.
Ex: \PARSEDATE(“22.11.2025 16:45:05”, “dd.mm.yyyy hh:mm:ss”) = DATE(2025,11,22) + TIME(16,45,5)
\PARSEDATE(“12/1/75″,”m/d/y”) = DATE(1975,12,1)
TODO: Currently does not support dates with month names written out (formats with mmm or mmmm, such as “12-apr-2015” or “April 12, 2025”)
Blog
Now, some lambdas are so much fun they practically write themselves. This one wasn’t that at all. It still isn’t done, I am not satisfied with it, and by the time I add support for dates with month names, it will have reached the character limit for pasting it via the Name Manager.
I always wondered why Excel didn’t have a DATEVALUE function that would take any format not just the default system format, a reverse of FORMAT(DATE(y,m,d),format). Now I know. It’s a nightmare.
First of all, date formatting is lossy. And I don’t just mean situations where you skip a date element. Imagine a date format like “dmy”. How do you tell the 11th of January from the 1st of November? Should you try to parse it anyway? Should you throw an error? Not all dates will be ambiguous, should you decode the unambiguous ones?
Another issue is date format decoding itself. Excel lives by its own rules. MM means minute when it comes after hh or before ss. Otherwise it means month. God only knows what other caveats there are. In a stroke of genius, I decided to forego parsing formats altogether and used the datetime of “02/03/1988 16:05:07” (that’s the 32204.67022 constant) as a template to apply the user-supplied format to, and parse the result. Good thing there are just enough digits.
This function will never be perfect. Not all calendars start on the birth of Jesus. I will look into it some more, it would be fun to learn about some other calendars there are out there. I will definitely extend this function to support month names in text format. If nothing else, once the function exceeds the Name Manager character limit, it might give some users an incentive to purchase the premium workbook.