Convert string into a decimal time

[#1]

Hello everyone!

I have a string field filled with values like this:

72 Hrs  0 Min

90 Hrs  53 Min

99 Hrs  0 Min

Hrs   Min

225 Hrs  0 Min

0 Hrs  58 Min

1520 Hrs  53 Min

I need to convert it to decimal format to make furter calculations

Somebody know how to do it?

Thanx very much!

Pablo

[JonnyPoole · Accepted solution]

It can be done with regular string functions.  Not sure what to do with the last row... what do you want ?

Load

  *,

  Hours + Minutes/60 as HourInDecimals;

LOAD

  A as Time,

  num(left(A, Index(A,'Hrs')-2)) as Hours,

  num(mid(A, Index(A,'Hrs') + 5 , Index(A,'Min')- (Index(A,'Hrs')+5)))  as Minutes

FROM

(ooxml, no labels, table is Sheet1);

[#3]

Thanx very much Jonathan!