# Calculating Estimated Arrival Date & Time

#### Fifth Wheel Logistix

##### New Member
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.

 Miles days Days hours Hours Hours Minutes 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 Miles Days Hours Minutes 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

### 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
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

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.

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.

### Which adblocker are you using?

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

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