# Better way to calculate string formula in excel 2016/O365?

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:

Use this SUM as an Array Formula:

=SUM(
(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.

• 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