[Solved] Adding rest breaks into trip

Discuss the spreadsheet application

[Solved] Adding rest breaks into trip

Postby RusselB » Tue Nov 26, 2019 2:23 am

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.
Attachments
Ottawa.ods
(32.53 KiB) Downloaded 10 times
Last edited by MrProgrammer on Sun Dec 08, 2019 9:20 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5787
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Adding rest breaks into trip

Postby MrProgrammer » Thu Nov 28, 2019 11:19 pm

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 10 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.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3939
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Adding rest breaks into trip

Postby RusselB » Fri Nov 29, 2019 1:08 am

WOW!!!.. You continually impress me with your knowledge.
I don't think I would've even considered using Data -> Subtotals (etc.)
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5787
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON


Return to Calc

Who is online

Users browsing this forum: No registered users and 4 guests