Text&number characters to change to time

[Izabela80]

Hello,

I've stumbled upon a problem of changing text&numbers into actual time values which could be calculated in the sheet.
Anyone able to help on this one?
This is part of a report which will contain ca. 5k rows of data and I will need to do this change frequently, i.e. each time I download a source report.
I'm attaching the problematic column.
I need to see time in any format.

[Glenn Kennedy]

Do you want HH:MM time or decimal time as the result?

Excel does not like negative times... unless you use the 1904 date system for that file... in which case, any and all other dates in that file will be out by 1462 days...

[Izabela80]

Do you want HH:MM time or decimal time as the result?

Good question...
I think I could start with decimal hours. From there I will be able to transform it to any other time format, it's just that I'm stuck with this text unreadable to excel

[Glenn Kennedy]

I have overcomplicated this, for sure...

Please Login or Register  to view this content.

In PL, you may need ; instead of ,

[Izabela80]

Thank you, Glenn - that's one hell of a chunky formula!

[Phuocam]

Or try this:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(COUNTIF(A1,"*h*"),"","0 h")&A1,"-","")," h",":")," m","")*24*SIGN(--LEFT(A1,2))

[Phuocam]

Note:

Row 134 ('-1 h 43 m), my formula returns -1.71667