Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Choose Language Hide Translation Bar
Highlighted
Level I

## 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
Highlighted
Staff

## 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
Highlighted
Staff

## 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
Level VII

## 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)?"
);

If( Is Missing( match[3] ), 0,    In Days( 	Num( match[3] ) ) ) +
If( Is Missing( match[5] ), 0,   In Hours(	Num( match[5] ) ) ) +
If( Is Missing( match[7] ), 0, In Minutes( 	Num( match[7] ) ) )``````
Article Labels

There are no labels assigned to this post.