GOAL: evaluate "2 Days 16 Hours 30 Minutes" to seconds.

I searched the web and found no fitting answer, i desire a 100% formula solution. Limitations: no VBA, avoid name manager, only day/hour/minute, spaces exist.

current solution i have working: enter image description here


Use this SUM as an Array Formula:

  (ISNUMBER(SEARCH("Day",TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",999)),(ROW($XFD$1:INDEX(XFD:XFD,LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1)))*999,999)))))*(IFERROR(60*60*24*TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",999)),(ROW($XFD$1:INDEX(XFD:XFD,LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1))-1)*999+1,999)),0))
+ (ISNUMBER(SEARCH("Hour",TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",999)),(ROW($XFD$1:INDEX(XFD:XFD,LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1)))*999,999)))))*(IFERROR(60*60*TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",999)),(ROW($XFD$1:INDEX(XFD:XFD,LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1))-1)*999+1,999)),0))
+ (ISNUMBER(SEARCH("Minute",TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",999)),(ROW($XFD$1:INDEX(XFD:XFD,LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1)))*999,999)))))*(IFERROR(60*TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",999)),(ROW($XFD$1:INDEX(XFD:XFD,LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1))-1)*999+1,999)),0))

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when Exiting edit mode.

This iterates each word in the string. If it finds Day(s),Hour(s),Or Minute(s) It does the proper multiplication with the number in front of that word. Then it adds the results.

enter image description here

|improve this answer|||||
  • i forgot to mention that i desire to avoid array formulas so i accept the answer. Nice & quick reply! – Ragnar Von Lodbrok May 22 '19 at 16:18
  • It cannot be done without array or array type formula, vba, or using evaluate in the name manager. – Scott Craner May 22 '19 at 17:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.