Format for Estimated Time of Arrival

[excelwhat]

I am trying to create a sheet that shows how long a specific drive will take, by dividing the mileage in one column by 55 mph (which is not in a column) and get and answer that shows the length of time it takes in Hours : Minutes.

I already have the formula, but I cannot find the correct Formatting to produce the Hour : Minutes. Is there one?

 

[buxtongt]

Welcome to MrExcel.

You can take the total minutes (ex. in A1) and do:

=time(0,a1,0)

then format that cell as h:mm. That should show what you want. Hope this helps.

 

[Andrew Poulsom]

Welcome to MrExcel.

You can use:

=Mileage/55/24

formatted as Time.

 

[excelwhat]

Thanks for the Welcome, and your quick replies.

Right now I have a column with mileage (F) a column where I want the ETA to be (G).

The formula I am currently using in cell G1 is this:

= F1/55

The Format I am using is Number w/ 2 decimal places. This doesn't really serve me well because .00 side of the formatting is in .0 - .99 increments.

I need :0 - :59 increments.

Is there a format that can produce this type of answer?

 

[buxtongt]

Andrew's solution does that (nicer than mine). For 110 it will give you 2:00, for 112 it gives you 2:02.

 

[barry houdini]

If you follow Andrew's suggestion then that would make the formula in G1

=F1/55/24

format G1 as h:mm

 

[excelwhat]

YES!! I believe this worked!!

Thank you very much!! All of you!!

Merry Christmas!!

 

[excelwhat]

Actually... I'm running into a new problem.

As I stated above, in G1 I wrote the formula = F1/55/24

Then I clicked and dragged this formula to G60 so it would repeat.

I then formatted the entire column to suggested h:mm

Looked good until I filled F1 - F60 with the Mileages, and now I'm getting innacurate answers.

Example:

F2 (107 miles) = G2 ( 1:56 ) **Correct

F8 (1425 miles) = G8 ( 1:54 ) **Not Correct



What would be causing this problem?

 

[shg]

Change the format to [h]:mm

 

[excelwhat]

I believe what's happening is that the Format h:mm is actually converting the answer to the time of day without the am/pm.

So perhaps the formula ( = F1/55/24 ) or the format ( h:mm ) are not compatible for creating an actual Estimated Time of Arrival.

Example of what I am trying to accomplish:

870 miles / 55 mph = 15 hours : 45 minutes of driving

It seems so odd that there are formulas for mathematics I didn't even know of, but nothing that can produce this type of answer.

Please help!