Estimated Travel Time

[ctexcelguy]

I've been trying to figure out a formula that will tell me how long a road trip will take at a specific mileage at a specific speed including rest stops. For example:

90 miles [variable]
60 MPH [variable]
= 1.5 hours of non stop travel
15 min of rest every 1.5 hours [variable]
= 0:1:45 (days: hours: minutes) based on a 24 hour day
based on only 8 hours [variable] travel hours per day this equals 0.22 travel days or 1.5 hours

Problem is when I change the mileage to say 180 it calculates 3:15 not 3:30 hours, etc
and I'd like the final (days: hours: minutes) to be based on an 8 [variable] hour travel clock not 24.

I've attached the file for a better review.
What am I doing wrong? Estimated Trip Time and Costs.xlsx

Error in time calc. Should be:
90 miles = 1.5 hours or 1:45 with stops
180 miles = 3.0 hours or 3:30 with stops
270 miles = 4.5 hours or 5:15 with stops
360 miles = 6.0 hours or 7:0 with stops

Thanks for any assistance.

[windy58]

See attached which calculates your examples correctly.

Have used a vlookup for the breaks.

Windy

[ctexcelguy]

Thank you thank you! This works great. One other question can I change the Day Hour Minute formula to reflect an x hour day vs just 24 hour day?

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.