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.
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,387
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,970
Messages
5,627,923
Members
416,282
Latest member
fchagas97

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