How to fix a duration with missing colon :

[#1]

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 

[#2]

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

Or

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;

[#3]

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

[#4]

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!

[#5]

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?

[#6]

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.

[#7]

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...

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

[#8]

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