Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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);
Thanx very much Jonathan!