# Convert text Days, hours, minutes to total hours.

1. ## Convert text Days, hours, minutes to total hours.

Afternoon all,

Im looking for help with the below image. I'm trying to convert this data from its current format, to total hours rounded up to the nearest full hour.

Capture.PNG

Example document also included.

2. ## Re: Convert text Days, hours, minutes to total hours.

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

3. ## Re: Convert text Days, hours, minutes to total hours.

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)

4. ## Re: Convert text Days, hours, minutes to total hours.

That is a very clever solution!

5. ## Re: Convert text Days, hours, minutes to total hours.

I agree with Bernie.

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

6. ## Re: Convert text Days, hours, minutes to total hours.

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

7. ## Re: Convert text Days, hours, minutes to total hours.

Amazing thanks all

There are currently 1 users browsing this thread. (0 members and 1 guests)