Convert text Days, hours, minutes to total hours.

Please try at F5

=ROUNDUP(SUMPRODUCT(TEXT(MID("00"&SUBSTITUTE(B5,",",", "),FIND({"d","h","m"},"00"&SUBSTITUTE(B5,",",", ")&"dhm")-4,3),"0;;;\0")/{1,24,1440})*24,0)

In E5, enter

=IFERROR(TRIM(LEFT([@[Time in location]],FIND("day",[@[Time in location]])-1))*24,0)+IFERROR(VALUE(TRIM(MID([@[Time in location]],FIND("hour",[@[Time in location]])-3,3))),IFERROR(VALUE(TRIM(LEFT([@[Time in location]],FIND("hour",[@[Time in location]])-1))),0))+IF(ISERROR(FIND(" 0 minute",[@[Time in location]])),1,0)

I wasn't sure if you would ever have minutes not included, or tagged as 0 minute(s) - I thought it would be 0 minutes, but if there are no minutes included when there are full hours, just change

+IF(ISERROR(FIND(" 0 minute",[@[Time in location]])),1,0)


to

+IF(ISERROR(FIND("minute",[@[Time in location]])),0,1)

That is a very clever solution!

I agree with Bernie.

I will have to study that one for a while. It goes in my "to keep file".

Bernie and Dave, Thank you.

Here another version of the formula

=SUM(TEXT(MID(0&A5,SEARCH({"d","h","m","se"},0&A5&"dhmise")-3,2),"0;;;\0")*{1,"1:0","0:1","0:0:1"})

19.jpg

https://www.facebook.com/XcWizard/po...60604607341301