Converting Text into Hours

[Ron Purpura]

Hello all
I am stuck and looking for some help please
Attached is the file. You will see in column A that I have a text value that is broken up in Days and Hours and I would like to convert it to hours in column B. I have somewhat of a formula but running into problems whenever the Day is greater than 1 character.....i.e 9 D to 10 D will not calculate incorrectly ( Cell A2 A141 A160 etc)

Thanks in advance

[Pete_UK]

Try this in B2:

=IF(COUNTIF(A2,"*d*"),LEFT(A2,FIND("d",A2)-1),0)*24 + IF(COUNTIF(A2,"*h"),SUBSTITUTE(RIGHT(A2,3),"h","")*1,0)

then copy down.

Hope this helps.

Pete

[JieJenn]

I noticed that the ones with minutes are all count as zero so I am ignoring this. But if I were approach to a problem like this, I would create helper columns to extract day and hour values individually in case if I need to go back and modify the formula. See attached.

[AlKey]

This should calculate Days, Hours & Minutes

Enter formula in B2 and copy down

Please Login or Register  to view this content.

[Ron Purpura]

Thank you Pete !

[Ron Purpura]

Thank you ALKey