Hi all, I have an issue that I haven't encountered before... I have an Excel file with 87 columns of durations that should be in hh:mm However, they are in hhmm or hmm <-- note the missing : * If the duration is less than 10:00 it is three figures (9:59 is entered as 959) * If the duration is more than 9:59 it is four figures (11:52 is entered as 1152) I can handle this if it were 1 or 2 columns, but not 87. See attached file. All help is highly appreciated! Cheers, Christian example for JMP com.jmp

Formula for the quick formula method when the columns are character columns. This makes assumption that values which are already duration "formatted" will have zero padding for hour (such as 09:00:00) numonly = Substitute(_relative_from_X__1, ":", ""); // remove ":" hrm = Left(numonly, 4); // take 4 characters from left val = Num(hrm); // convert to numeric for calculations h = Floor(val / 100); // take only hundreds m = Mod(val, 100); // remainder t = h * 60*60 + m * 60; // convert to seconds I create new columns here so I can easily compare them to the starting point. After creating formulas, do required type changes using standardize attributes (data type, modeling type, format) View more... (Highlight to read)This will create new data columns, you might want to use in place data instead// Transform column Data Table("example for JMP com v2") << Apply Formula( Columns(Group(:X__1, 12), Ref("_relative_from_X__1")), Formula( numonly = Substitute(_relative_from_X__1, ":", ""); hrm = Left(numonly, 4); val = Num(hrm); h = Floor(val / 100); m = Mod(val, 100); t = h * 60 * 60 + m * 60; ), Output(New Static) ) << Run Formulas; // Standardize column attributes Local({old dt = Current Data Table()}, Current Data Table(Data Table("example for JMP com v2")); For Each({col, index}, {:X__1 2, :X__2 2, :X__3 2, :X__4 2, :X__5 2, :X__6 2, :X__7 2, :X__8 2, :X__9 2, :X__10 2, :X__11 2, :X__12 2}, col << Set Data Type(Numeric, Format("Best", 12)) ); Current Data Table(old dt); ); // Standardize column attributes Local({old dt = Current Data Table()}, Current Data Table(Data Table("example for JMP com v2")); For Each({col, index}, {:X__1 2, :X__2 2, :X__3 2, :X__4 2, :X__5 2, :X__6 2, :X__7 2, :X__8 2, :X__9 2, :X__10 2, :X__11 2, :X__12 2}, col << Set Modeling Type("Continuous") ); Current Data Table(old dt); ); // Standardize column attributes Local({old dt = Current Data Table()}, Current Data Table(Data Table("example for JMP com v2")); For Each({col, index}, {:X__1 2, :X__2 2, :X__3 2, :X__4 2, :X__5 2, :X__6 2, :X__7 2, :X__8 2, :X__9 2, :X__10 2, :X__11 2, :X__12 2}, col << Format("hr:m", 12) ); Current Data Table(old dt); ); This will create new data columns, you might want to use in place data instead // Transform column Data Table("example for JMP com v2") << Apply Formula( Columns(Group(:X__1, 12), Ref("_relative_from_X__1")), Formula( numonly = Substitute(_relative_from_X__1, ":", ""); hrm = Left(numonly, 4); val = Num(hrm); h = Floor(val / 100); m = Mod(val, 100); t = h * 60 * 60 + m * 60; ), Output(New Static) ) << Run Formulas; // Standardize column attributes Local({old dt = Current Data Table()}, Current Data Table(Data Table("example for JMP com v2")); For Each({col, index}, {:X__1 2, :X__2 2, :X__3 2, :X__4 2, :X__5 2, :X__6 2, :X__7 2, :X__8 2, :X__9 2, :X__10 2, :X__11 2, :X__12 2}, col << Set Data Type(Numeric, Format("Best", 12)) ); Current Data Table(old dt); ); // Standardize column attributes Local({old dt = Current Data Table()}, Current Data Table(Data Table("example for JMP com v2")); For Each({col, index}, {:X__1 2, :X__2 2, :X__3 2, :X__4 2, :X__5 2, :X__6 2, :X__7 2, :X__8 2, :X__9 2, :X__10 2, :X__11 2, :X__12 2}, col << Set Modeling Type("Continuous") ); Current Data Table(old dt); ); // Standardize column attributes Local({old dt = Current Data Table()}, Current Data Table(Data Table("example for JMP com v2")); For Each({col, index}, {:X__1 2, :X__2 2, :X__3 2, :X__4 2, :X__5 2, :X__6 2, :X__7 2, :X__8 2, :X__9 2, :X__10 2, :X__11 2, :X__12 2}, col << Format("hr:m", 12) ); Current Data Table(old dt); );

Yes, unfortunately, this is from the real data. I had selected two columns that did not have this issue, as I thought I could just use recode. But I thought wrong, or at least I realized that using recode on 84 columns is too much for what I can bear. Yes, the seconds are always 00.

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to fix a duration with missing colon :

Stats_Like_Jazz

Hi all,

I have an issue that I haven't encountered before...

I have an Excel file with 87 columns of durations that should be in hh:mm

However, they are in hhmm or hmm <-- note the missing :

If the duration is less than 10:00 it is three figures (9:59 is entered as 959)
If the duration is more than 9:59 it is four figures (11:52 is entered as 1152)

I can handle this if it were 1 or 2 columns, but not 87.

See attached file.

All help is highly appreciated!

Cheers, Christian

example for JMP com.jmp

Accepted answers

All comments

jthi

Plenty of options (I think all off these should work)

1. Convert them all to strings

2. Add 0 to the start

3. Take last 4 characters

4. Insert ":" as third character

5. Convert them to numeric with duration formatting

Names Default To Here(1);

dt = Current Data Table();

cols = dt << Get Column Names("String");

For Each({colname}, cols,
	Column(dt, colname) << Set Data Type("Character");
	Eval(EvalExpr(
		dt << Apply Formula(
		Columns(Expr(NameExpr(AsColumn(dt, colname)))),
			Formula(
				s = Right("0" || Expr(NameExpr(AsColumn(dt, colname))), 4);
				Left(s, 2) ||":"||Right(s,2)
			),
			Output("In Place")
		) << Run Formulas;
	));
);

For Each({colname}, cols,
	Column(dt, colname) << Set Data Type(Numeric, Format("hr:m", 30), Input Format("hr:m")) << Set Modeling Type("Continuous");
);

Names Default To Here(1);

dt = Current Data Table();

cols = dt << Get Column Names("String");

dt << Begin Data Update;
For Each({colname}, cols,
	For Each Row(dt,
		s = Char(Column(dt, colname)[]);
		If(Column(dt, colname)[] < 1000,
			s = "0" || s
		);
		Column(dt, colname)[] = In Format(Left(s, 2) || ":" || Right(s, 2), "hr:m");
	);
	Column(dt, colname) << Format("hr:m", 30);
);
dt << End Data Update;

Or just use the numbers and "convert" them to JMP time format

Names Default To Here(1);

dt = Current Data Table();

cols = dt << Get Column Names("String");

dt << Begin Data Update;
For Each({colname}, cols,
	For Each Row(dt,
		val = Column(dt, colname)[];
		h = Floor(val / 100);
		m = Mod(val, 100);
		t = h * In Hours(1) + m * In Minutes(1);
		Column(dt, colname)[] = t;
	);
	Column(dt, colname) << Format("hr:m", 30);
);
dt << End Data Update;

jthi

And the last method can be quite easily done from JMP interactively also.

1. Select all columns

2. Right click on column header

3. Create Formula quick column

4. Replace the formula using the relative styling

h = Floor(_relative_from_Col 1 / 100);
m = Mod(_relative_from_Col 1, 100);
t = h * In Hours(1) + m * In Minutes(1);

5. Change output to In Place Data

6. Click "Replace x"

7. Change format to duration hr:m using Standardize Attributes as currently you just have seconds visible with Best formatting

8. After clicking OK or Apply

And JMP is nice enough to write script for you

// Transform column
Data Table("example for JMP com") << Apply Formula(
	Columns(Group(:Col 1, 2), Ref("_relative_from_Col 1")),
	Formula(
		h = Floor(_relative_from_Col 1 / 100);
		m = Mod(_relative_from_Col 1, 100);
		t = h * In Hours(1) + m * In Minutes(1);
	),
	Output("In Place")
) << Run Formulas;


// Standardize column attributes
Local({old dt = Current Data Table()},
	Current Data Table(Data Table("example for JMP com"));
	For Each({col, index}, {:Col 1, :Col 2}, col << Format("hr:m", 10));
	Current Data Table(old dt);
);

txnelson

Here is how I would do it:

Names Default To Here( 1 );
dt = Current Data Table();

colNames = dt << get column names( contiuous, string );
For Each( {col}, colNames,
	Show( col );
	For Each Row(
		As Column( col ) = Informat(
			Substr( Char( As Column( Col ) ), 1, Length( Char( As Column( Col ) ) ) - 2 ) || ":" ||
			Substr( Char( As Column( Col ) ), Length( Char( As Column( Col ) ) ) - 1, 2 ),
			"hr:m"
		)
	);
	column(dt,col) << format( "hr:m" );
);

Stats_Like_Jazz

Hi Jarmo, thank you so much!

Your first script works magic!

Except that sometimes, I just realized, the time is entered as hh:mm:ss

Is there a way to work around this as well? See example v2.

Cheers, C

P.S. I owe you a coffee or beer the next time I see you!

example for JMP com v2.jmp

jthi

If that is from real data (character columns with different formats)? Original example had numeric data if I remember correctly. Are the seconds always 00 as they are in the example?