Tekst to hours

Hi all,

I have a overview of time spent and written like this:

4 days, 5 hours, 19 minutes
1 day, 1 hour
7 hours, 5 minutes
2 hours
1 hour
30 minutes

I would like to convert it to hours.

example (1 day = 8 hours):
1 day, 1 hour = 9
30 minutes = 0.5

Column B should only give the amount of hours. See attached

I use Excel 2010

=IFERROR(TRIM(MID(SUBSTITUTE(","&A2,",",REPT(" ",LEN(A2))),FIND("day",SUBSTITUTE(","&A2,",",REPT(" ",LEN(A2))))-LEN(A2),LEN(A2))),0)*8+
IFERROR(TRIM(MID(SUBSTITUTE(","&A2,",",REPT(" ",LEN(A2))),FIND("hour",SUBSTITUTE(","&A2,",",REPT(" ",LEN(A2))))-LEN(A2),LEN(A2))),0)+
IFERROR(TRIM(MID(SUBSTITUTE(","&A2,",",REPT(" ",LEN(A2))),FIND("min",SUBSTITUTE(","&A2,",",REPT(" ",LEN(A2))))-LEN(A2),LEN(A2))),0)/60

Please Login or Register  to view this content.

Try this out:

Formula:

Please Login or Register  to view this content.

=TEXT((IFERROR(TRIM(MID(SUBSTITUTE(","&A2,",",REPT(" ",LEN(A2))),FIND("day",SUBSTITUTE(","&A2,",",REPT(" ",LEN(A2))))-LEN(A2),LEN(A2))),0)*8+
IFERROR(TRIM(MID(SUBSTITUTE(","&A2,",",REPT(" ",LEN(A2))),FIND("hour",SUBSTITUTE(","&A2,",",REPT(" ",LEN(A2))))-LEN(A2),LEN(A2))),0)+
IFERROR(TRIM(MID(SUBSTITUTE(","&A2,",",REPT(" ",LEN(A2))),FIND("min",SUBSTITUTE(","&A2,",",REPT(" ",LEN(A2))))-LEN(A2),LEN(A2))),0)/60)/24,"[H]:MM")

Please Login or Register  to view this content.

Originally Posted by Glenn Kennedy

Try this out:

Formula:

Please Login or Register  to view this content.

Good Idea Glen
Don't think otherwise, I modified your formula (I really impressed with the formula / idea)
=TRIM(IFERROR(LEFT(A2,SEARCH("Day",A2)-1),0))*8+LOOKUP(60,--RIGHT(TRIM(IFERROR(LEFT(A2,SEARCH("HOUR",A2)-1),0)),{1,2}))+LOOKUP(60,--RIGHT(TRIM(IFERROR(LEFT(A2,SEARCH("mIN",A2)-1),0)),{1,2}))/60

Please Login or Register  to view this content.

thanks for the quick answers, I downloaded Glann's file and is working. As soon as I copy and paste the modified formula from NFLsales Excel keeps telling formula is wrong. Any change to upload a excel example with new formula?

Done:

Please Login or Register  to view this content.

See the attached file