I need help with a formula to calculate standard time to military time. The minutes part of the equation needs to be from 60 to 100 minutes. For example Clock in time is 7:52:03 and it should be 7:86:05 (multiplying the minutes by 1.66). The clock out time is 15:12:11 and it should be 15:20:18. The standard subtraction equals 7:20:08 however, it should be 7:34:21.

re: "the minutes part of the equation needs to be from 60 to 100 minutes" First, I have never known a "military hour" to have 100

minutes. It has 60 minutes just like any other hour, but is often expressed as a decimal portion of an hour. e.g. 15 minute = .25 of an hour.I don't think I have ever seen military time expressed as 7:86:05. 7.86, Yes, but 7:86, No, at least not in my experience. I think that that format would be very confusing.

If I saw 15:25 (in 24 hour time format) or 1525 (in my experience with military time) I would say that it is 25 minutes after 3 PM, not 15 minutes after 3 PM. Any value that is less than 60 and used in the minutes or seconds portion of the value could be taken 2 different ways.

As far as the math part of your question, this might help.

In Excel, if you multiply a time by 24 and format the cell as a number, you will get the decimal equivalent, but it will not contain the colons. e.g.

7:52:02 * 24 = 7.8675

15:12:11 * 24 = 15.2030555555556You can then do your math with the decimal values and get:

7.33555555555556

If you wanted to, I guess you could then convert that to a "colon delimited" string, but I wouldn't.

You might want to see if there is anything here that might help:

http://www.cpearson.com/excel/datet...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

DD is correct. This isn't really military time; it's just a converting 60 seconds into percentages of an hour. So, the correct calculation is dividing the minutes by 60, not multiplying by 1.66. I have a an old chart which already has these conversions done, if you would like me to post it, I can.

Ask Your Question

Weekly Poll