r/sheets • • Oct 14 '26

Solved Scheduling software broken

So our scheduling software normally generated walk sheets like this

NameShiftBreak
Jake5:00 PM - 9:00 PM7:00 PM - 7:15 PM

But now it's generating them like this:

NameShiftBreak
Jake5:00 PM - 7:00 PM
Jake7: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.

2 Upvotes

22 comments sorted by

2

u/AdministrativeGift15 Oct 15 '26

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.

1

u/Vercalos Oct 15 '26

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

2

u/AdministrativeGift15 Oct 16 '26

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

1

u/Vercalos Oct 16 '26

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.

1

u/AdministrativeGift15 Oct 16 '26

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

1

u/Vercalos Oct 16 '26

Just the time between the two intervals.

2

u/AdministrativeGift15 Oct 16 '26

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?

1

u/Vercalos Oct 16 '26 edited Oct 16 '26

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.

2

u/AdministrativeGift15 Oct 16 '26

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

1

u/Vercalos Oct 16 '26

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

→ More replies (0)

1

u/molybend Oct 14 '26

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.

1

u/Vercalos Oct 14 '26

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

1

u/mommasaidmommasaid Oct 14 '26 edited Oct 14 '26

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

1

u/Jaded-Function Oct 15 '26

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

1

u/bogdanelcs Oct 19 '26

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.