Excel functions to covert X days, x hours, X minutes in actual minutes

Question

I've some data in excel in the following manner.

• 9 hours 16 minutes 2 seconds
• 4 days 7 hours 41 minutes 44 seconds
• 1 day 8 hours 3 minutes 49 seconds
• 33 minutes 4 seconds

I need to convert the data in the minute's format. I tried excel's find, left, right, mid functions but couldn't able to fetch the desired result.

Can anyone help?

Answer 1

Use the following array formula:

=SUM(IFERROR(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",999)),(ROW($1:$4)-1)*2*999+1,999))*CHOOSE(MATCH(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",999)),(ROW($1:$4)-1)*2*999+999,999))&"*",{"days","hours","minutes","seconds"},0),(60*24),60,1,(1/60)),0))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter.