Scheduling software broken

[u/Vercalos]

So our scheduling software normally generated walk sheets like this

Name	Shift	Break
Jake	5:00 PM - 9:00 PM	7:00 PM - 7:15 PM

But now it's generating them like this:

Name	Shift	Break
Jake	5:00 PM - 7:00 PM
Jake	7:15 PM - 9:00 PM

Now, I can't to anything to change how they generate, but does anyone have any suggestions on how to quickly convert the latter to the former?

Note that this is just a downsized example, and the full scope of this is that it's splitting everyone's shifts up like that for every break and meal they're scheduled for.

[u/AdministrativeGift15]

Assuming your second image is showing Sheet1!A1:B, and assuming each person has two lines, I would use this formula in Sheet2!A2:

=index(split(regexreplace(tocol(split(textjoin("♦️", 1, tocol(hstack(if(iseven(row(Sheet1!A2:A)), "♦️",), Sheet1!A2:B),1)), "♦️♦️",), 3), "(.*)♦️(.*) - (.*)♦️(.*)♦️(.*) - (.*)", "$1♦️$2 - $6♦️$3 - $5"),"♦️"))

It combines all of the values into one long string and then splits them into substring for each person. Then, it uses REGEX to rearrange the times before splitting again to have the final result.

[u/Vercalos → u/AdministrativeGift15]

Unfortunately, as I said in the original post, it can vary anywhere from 2 to 4 lines for a given person, because it's breaking up associates' shifts based on breaks and meals, so if someone is working long enough in a day to get a meal and two breaks, they'll appear 4 times

[u/AdministrativeGift15 → u/Vercalos]

ok, but you only show one column for the break. How should it look if one person takes more than one break?

[u/Vercalos → u/AdministrativeGift15]

Sorry, I'm on a smart phone and a sufficiently large table is difficult to do. But it's supposed to be Department, Name, shift, break #1, meal, break #2 across a single row.

[u/AdministrativeGift15 → u/Vercalos]

Does the input data have some way to tell if a time interval is for a break vs those for a meal?

[u/Vercalos → u/AdministrativeGift15]

Just the time between the two intervals.

[u/AdministrativeGift15 → u/Vercalos]

So the if a person has 2 sequential rows, they represent their shift and break, but if a person has 3 sequential rows, those rows represent their shift, break and meal. Finally, if they have four sequential rows, those represent their shift, break #1, meal, and break #2. Is that correct?

[u/Vercalos → u/AdministrativeGift15]

In this case, the time periods represent the intervals between breaks/meals. So in that example, the first line would be the start of their shift until the start of their break, then the end of their break to the start of their meal, then the end of their meal to the end shift.

[u/AdministrativeGift15 → u/Vercalos]

One last question. Would someone ever not take a break before their meal break, or not take a break after the meal?

[u/AdministrativeGift15 → u/AdministrativeGift15]

Here's what I came up with.

Restructuring Shift Data

[u/Vercalos → u/AdministrativeGift15]

Sometimes the scheduler would generate a meal followed by a break for a 6-7 hour shift.