Subscribe Bookmark RSS Feed

Convert character column representing duration to numeric

jenny1

Community Member

Joined:

Nov 13, 2017

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
Justin_Chilton

Joined:

Aug 27, 2015

Solution

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
Justin_Chilton

Joined:

Aug 27, 2015

Solution

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
ih

Community Trekker

Joined:

Sep 30, 2016

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

//Add up duration
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] ) ) )