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.
Share
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.2030555555556
You 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.