Trying to calculate times used in planning convoys and cannot seem to make the calculations work (because of mixed formatting?): There are several of them.

1. Calculate arrive times as follows: The arrive time at the SP is the same as the SP time. To calculate the arrive time at the first CP, take the distance from the SP to the first CP, divide by the planned rate of march, and multiply by 60 (minutes). Add this amount of time distance to the arrive time at the SP to determine the arrive time at the first CP.

and now the problems start:



Distance from SP to first CP: 10 km
March rate: 50 KMIH
Solution: 10 ÷ 50 = .20 hours x 60 = 12 minutes

If the arrive time at the SP is 0800, then the arrive time at the first CP will be 08:12.

ISSUE: when I do the calculations on a worksheet the formula cells do not accept h:mm formatting and do not add the 12 minutes to 0800 (or 8 AM) and throughout the entire rest of the solution set to compute arrival times, add in rest times, and fuel or maintenance stops the units of time just will not add up.


Distance from Start Point (SP) to first Check Point (CP)
KM March Rate
KMIH 10 50 Time to arrive at CP1
(formula) 12 00:12 '=((C2/D2)*60)

SP DEPART TIME CP 1 ARRIVAL TIME 8 8.00
The CP 1 arrival time should be 08:12 and not 8.00.
There are many more instances where the arithmetic works with numbers, but not with any thing formatted as time or custom [h]:mm or any time like format.

I appreciate any help that can solve the problem I'm having.

Thanks in advance.

Please post a sample sheet (no pics) showing what you have and expected results. Thank you

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

I uploaded a workbook with the requested information.

Attached Files

BookForForum.xlsx (21.8 KB, 11 views)

You have to understand how XL sees time.

To XL a day is 1, an hour 1/24, a minute 1/1440 etc

So if you enter =1/1440 in a cell and format as , say h:mm ( using custom format) XL shows 00:01

Formatting is ONLY the way XL prsents things to the user, it is not the underlying value ( when time and date are concerned). So formatting a number to time/ date will not do what you expect.

In you example, in cell L12 enter =((H12/J2)*60)/1440 and custom format as h:mm

XL will show you 00:12 as expected ( the background value is 1/120)

Perhaps read http://www.cpearson.com/excel/datetime.htm to understand better how it works

ANother tip: when working with date/time DO NOT realign cell contents manually before you're satisfied all is OK.
Numbers always right align automatically and text aligns left. Sometimes one sees 10:00 that could be text( usually imported files). This way you can easily spot what goes wrong

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post