Page 1 of 1

[Solved] Adding rest breaks into trip

PostPosted: Tue Nov 26, 2019 2:23 am
by RusselB
The attached spreadsheet is a detailed itinerary for a trip being planned.
As the trip consists of a lot of driving, I'd like to schedule rest breaks at 15 minutes after every 3 hours of driving, but with that timing restarting after each stop of 15 minutes or more.
Normally this wouldn't be much of a problem with a helper column referencing the times in column B, but some of the entries in that column are blank and some are text.
I'm generating the spreadsheet information for columns B-F over and over again as changes to the schedule need to be accounted for, so I can't just move/remove those entries.

Once again, thanks in advance to any and all that help, or even attempt to help.

Re: Adding rest breaks into trip

PostPosted: Thu Nov 28, 2019 11:19 pm
by MrProgrammer
RusselB wrote:I'm generating the spreadsheet information for columns B-F over and over again as changes to the schedule need to be accounted for, so I can't just move/remove those entries.
I view the task to be inserting rows at specified points, so I would use Data → Subtotals which is good for that purpose even though you don't want subtotals. In the attached file, I've cleared columns C through G since they just add clutter. I put 1 in H2 and formulas =H2+AND(J2;ISNUMBER(B2)), =IF(J2;B3;I2), =IF(ISNUMBER(B3);(B3-I3)>TIME(3;0;0);ISTEXT(B3)), and =IF(OR(ISTEXT(B2);ISTEXT(B3));0;K2+J2*TIME(0;15;0)) in H3:K3, then filled them down to row 541.

Then I select column H and use Data → Subtotals → Options → Uncheck Pre-sort → First group → Group by → Segment → Calculate subtotals for → Segment → Use Function → Max → OK → Data → Group and outline → Remove. Now you have a new line for each break for your notes. Adjust the times in column B by selecting K3:K552, Edit → Copy, select B3, Edit → Paste Special → Operations → Add → OK.
201911281430.ods
(47.42 KiB) Downloaded 12 times

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

Re: Adding rest breaks into trip

PostPosted: Fri Nov 29, 2019 1:08 am
by RusselB
WOW!!!.. You continually impress me with your knowledge.
I don't think I would've even considered using Data -> Subtotals (etc.)