Choose Language Hide Translation Bar

## Convert character column representing duration to numeric

I have a Character Column where the input value represents Duration (like: 4d 15h 5m ,2d 32m, 23h 52m, 18m), and I would like to convert it to a Numeric format :day:hr:m

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

## Re: Convert character column representing duration to numeric

There might be a more elegant way to do this, but I was able to get the below formula to work (also in the attached table). It splits your character column using a space delimiter into a list. For each of those items, it adds the appropriate amount of time based on the last character to a variable that gets returned at the end.

``````durs = Words( :CharacterDuration, " " );
val = 0;
For( i = 1, i <= N Items( durs ), i++,
number = Num( Substr( durs[i], 1, Length( durs[i] ) - 1 ) );
If(
Ends With( durs[i], "d" ), val += In Days( number ),
Ends With( durs[i], "h" ), val += In Hours( number ),
Ends With( durs[i], "m" ), val += In Minutes( number )
);
);
val;``````
Justin
2 REPLIES 2
Solution

## Re: Convert character column representing duration to numeric

There might be a more elegant way to do this, but I was able to get the below formula to work (also in the attached table). It splits your character column using a space delimiter into a list. For each of those items, it adds the appropriate amount of time based on the last character to a variable that gets returned at the end.

``````durs = Words( :CharacterDuration, " " );
val = 0;
For( i = 1, i <= N Items( durs ), i++,
number = Num( Substr( durs[i], 1, Length( durs[i] ) - 1 ) );
If(
Ends With( durs[i], "d" ), val += In Days( number ),
Ends With( durs[i], "h" ), val += In Hours( number ),
Ends With( durs[i], "m" ), val += In Minutes( number )
);
);
val;``````
Justin
Highlighted

## Re: Convert character column representing duration to numeric

Another way, not more elegent but with more control over input:

``````//Parse Duration String
match = Regex Match(
:Character Duration,
"(([\d\.]+)d)? ?(([\d\.]+)h)? ?(([\d\.]+)m)?"
);