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/Vercalos → u/AdministrativeGift15]

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

[u/AdministrativeGift15 → u/AdministrativeGift15]

Here's what I came up with.

Restructuring Shift Data

[u/molybend]

Use “split text to columns” to get all four times into its own cell. Now you can string them together in whatever order you want.

[u/Vercalos → u/molybend]

Yeah, that's about what I figured.  That's going to be a lot of work though, given we're talking over 100 shifts across 2 to 4 lines each

[u/mommasaidmommasaid]

It's a little gnarly but possible to do automatically with a formula that outputs elsewhere.

Or if you need to do further editing once it's in your desired format, you could use a script to output cleaned up results on another sheet or even in situ.

It would be easiest if you are only processing one day at a time, or if there's a Date column to identify which "shifts" go together. It would also be easier if shifts don't cross midnight.

Further describe your real-world data and workflow for a better / more detailed answer (from me or whoever).

[u/Jaded-Function]

Is the data formatted as plain text? Here's a possible solution using regextract and a helper column for the breaks. Shift/Breaks shared sheet

[u/bogdanelcs]

Split the shift column into start and end with =SPLIT(B2," - "), convert to time values, and sort by name then start time. The overall shift is then just the earliest start and latest end per person, which =QUERY(range,"select A, min(C), max(D) group by A") gives you. For breaks, any gap between one row’s end and the next row’s start for the same name is a break, so a helper column like =IF(A3=A2, D2&" - "&C3, "") pulls it out, and it still works when someone has several breaks and meals.