Calculating Estimated Arrival Date & Time

Joined
Nov 9, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Greetings, I am a freight broker and I have looked into the forum for solutions but I'm unsure of what to look for, so if the answer is in the forum, please direct me to the thread.
I have created a spreadsheet where if:
total miles is enter into (A1) : 650
Currently, the spreadsheet will calculate as follows: Green means visible, every thing else is hidden.

Formulas (650 max miles driven/day......and these cells are formatted for number) 650 miles/day is the test number for accuracy. 1300 is for two days.

MilesdaysDayshoursHoursHoursMinutes
650 =SUM(A5/650) =INT(B5) =B5-TRUNC(B5) =B5-TRUNC(B5) =SUM(E5*10) =INT(F5)=F5-TRUNC(F5) =SUM(H5*60)

Examples of the functionality
This aspect of the sheet work fine, as far as I can tell.
1.1 table
Estimated Driver Time
MilesDaysHoursMinutes
650​
1​
0​
0​
651​
1​
0​
1​
1,300​
2​
0​
0​
1,301​
2​
0​
1​
1,132​
1
7
25

This part is the issue and question
The example for Depart & Arrive is based off 1 mile and it should say 19:31 but it says 19:32


Departure Date and Time​
Arrival Date and Time​
11/9/20 19:30
11/9/20 19:32

The departure date and time will be manually entered.
The estimated arrival date and time will be calculated based off the days, hours, and minutes from the above 1.1 table + the departure time = date and time.

I think that I have the cell formatted to calculate the date correctly [hh] but the time is not correct.


Issue: 1 mile is calculated as 22:09:14 and it converts to 2 minutes
2 miles is calculated as 20:18:28 and it converts to 4 minutes
3 miles convert to 6 minutes and so on.......

How do I get the date and time to calculate correctly?
Sorry if this post is confusing but that's how it is for me

Thanks in advance.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,717
Office Version
  1. 365
Platform
  1. Windows
Hi. It looks like you have 10 hour days so you have to take that into account. Lets say you have the number of miles in A5 and the start date time is a named range called 'start' you could try:

=start+INT(A5/650)+MOD(A5,650)*650/600/1440
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,632
Messages
5,838,476
Members
430,549
Latest member
jayjay2022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top