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

Need Help Assigning Column Based on Variable Times

OllieGRM

Hello,

I am attempting to create a new column in my data table 'Crew' based on an existing datetime column, LadleOpenTime (a small subset of ladleopentimes is shown below).

The times for the Crews (A,B,C, or D swing shifts) follow a fixed schedule shown by the excel table below:

The schedule is 7 days on, two days off, 7 days on, two days off, 7 days on 3 days off, then repeat for each crew.

Midnights (11:00PM-7:00AM), Days (7:00AM-3:00PM), Afternoons (3:00PM-11:00PM).

Is there any way to code this into jsl?

Appreciate any and all feedback

turn schedule.JPG

Accepted answers

txnelson

I believe the following will get you what you want. The only thing you need to do, is to set a date in the past which is a Sunday, where Crew C is working Midnights, Crew A is working Days, Crew D is working Afternoons, and Crew B is Off. This date starts the cycle of 28 days for your pattern. The formula determines the number of days from the latest beginning of the cycle, determines the Shift, and then finds which Crew was working that Shift. I don't have any actual data, but I believe the methodology will work. Try it with some of your real data. Remember, my StartingDate value is not correct. You need to change it as described above. Just create your new column called "Crew" and paste this formula into it. If you have any problems, please respond and provide a correct StartingDate, and attach a sample data table that has your LabelOpenTime column.

If( Row() == 1,
	groupAschedule = "DOOAAAAAAAOOMMMMMMMOOODDDDDD";
	groupBschedule = "ODDDDDDDOOAAAAAAAOOMMMMMMMOO";
	groupCschedule = "MMMMMOOODDDDDDDOOAAAAAAAOOMM";
	groupDschedule = "AAAOOMMMMMMMOOODDDDDDDOOAAAA";

	hourSchedule = "MMMMMMMDDDDDDDDAAAAAAAA";

	startingDate = Informat( "12/31/2017", "M/D/Y" );
);


targetTime = :LadleOpenTime;
If( Hour( targettime ) == 23,
	targetTime = targetTime + In Hours( 1 )
);

shift = Substr( hourSchedule, Hour( targetTime ) + 1, 1 );

offset = Mod( Date Difference( startingDate, targetTime, "Day" ), 28 ) + 1;

If(
	Substr( groupASchedule, offset, 1 ) == shift, theCrew = "A",
	Substr( groupBSchedule, offset, 1 ) == shift, theCrew = "B",
	Substr( groupCSchedule, offset, 1 ) == shift, theCrew = "C",
	Substr( groupDSchedule, offset, 1 ) == shift, theCrew = "D"
);
thecrew;

All comments

txnelson

If( Row() == 1,
	groupAschedule = "DOOAAAAAAAOOMMMMMMMOOODDDDDD";
	groupBschedule = "ODDDDDDDOOAAAAAAAOOMMMMMMMOO";
	groupCschedule = "MMMMMOOODDDDDDDOOAAAAAAAOOMM";
	groupDschedule = "AAAOOMMMMMMMOOODDDDDDDOOAAAA";

	hourSchedule = "MMMMMMMDDDDDDDDAAAAAAAA";

	startingDate = Informat( "12/31/2017", "M/D/Y" );
);


targetTime = :LadleOpenTime;
If( Hour( targettime ) == 23,
	targetTime = targetTime + In Hours( 1 )
);

shift = Substr( hourSchedule, Hour( targetTime ) + 1, 1 );

offset = Mod( Date Difference( startingDate, targetTime, "Day" ), 28 ) + 1;

If(
	Substr( groupASchedule, offset, 1 ) == shift, theCrew = "A",
	Substr( groupBSchedule, offset, 1 ) == shift, theCrew = "B",
	Substr( groupCSchedule, offset, 1 ) == shift, theCrew = "C",
	Substr( groupDSchedule, offset, 1 ) == shift, theCrew = "D"
);
thecrew;

OllieGRM

Thank you! That worked perfectly.

OllieGRM

Hi Jim,

I am trying to create a new column for a 3-turn reverse rotating schedule. D crew no longer exists, only A, B, and C remain.

The crews rotate shifts in reverse order going from midnights-->afternoons-->days-->midnights every 7 days. So a full cycle completes every 21 days.

On 7/1/19, A crew was on midnights, B crew was on days, and C crew was on afternoons.

I have tried modifying your previous formula as shown below:

I deleted the D crew schedule and associated if statements, replaced the A,B,C crew schedules with the new schedules, tweaked "offset" to check the days since the last beginning of the now 21 day cycle, inputted 7/1/19 as the start date, and changed "shift" to start at position +2 from the Hour(targetTime). This took some trialing and erroring.

Can you explain why changing the shift to start at position +2 from the Hour(targetTime) allowed this formula to work versus starting at position +1 as was done for the original 4crew schedule? I'm trying to better understand this so I can more effectively implement future changes.

Thanks,

Greg

If( Row() == 1,
	groupAschedule = "MMMMMMMAAAAAAADDDDDDD";
	groupBschedule = "DDDDDDDMMMMMMMAAAAAAA";
	groupCschedule = "AAAAAAADDDDDDDMMMMMMM";
	hourSchedule = "MMMMMMMMDDDDDDDDAAAAAAAA";
	startingDate = Informat( "7/1/2019", "M/D/Y" );
);
targetTime = :LadleOpenedTime;
If( Hour( targetTime ) == 23,
	targetTime = targetTime + In Hours( 1 )
);
shift = Substr( hourSchedule, Hour( targetTime ) + 2, 1 );
offset = Mod( Date Difference( startingDate, targetTime, "Day" ), 21 ) + 1;
If(
	Substr( groupASchedule, offset, 1 ) == shift, theCrew = "A",
	Substr( groupBSchedule, offset, 1 ) == shift, theCrew = "B",
	Substr( groupCSchedule, offset, 1 ) == shift, theCrew = "C"
);
thecrew;

txnelson

Without seeing your :LadleOpenTime data, I can not be positive in my statement, but it appears that your LadleOpenTime values are not aligned with your hourSchedule.

OllieGRM

Could it be due to the fact that the shifts are offset an hour? ie. 11-7, 7-3, 3-11, versus 12-8, 8-4, 4-12.

I changed the hourSchedule to "MMMMMMMDDDDDDDDAAAAAAAAM" and reverted the shift to start at position +1 and this worked all the same.

Thanks for taking the time to reply,

Greg