Adding/Subtracting military time

**criticalmass** · 11-05-2009, 12:46 AM

Wow, I'm frustrated. I've searched and found many people looking for solutions to circumstances similar to mine, but have yet to see a definitive answer that I can get to work for my particulars. It seems an issue that should be a basic function of excel as it is likely a popular operation, but apparently it's not.

I need to be able to enter a four digit number into cell A1 representing a military time (**NO COLON**, i.e. 0630) and another number in cell B1 with the same format. I then need to perform a computation on those two times in order to determine the duration between them. I need to output the duration in hours and tenths of hours (i.e. 3:12 would be 3.2 hours). The tenths of hours would be rounded to conform to the template:

Minutes ------- Tenths
1-2 ------------------ .0
3-8 ------------------ .1
9-14 ----------------- .2
15-20 --------------- .3
21-26 --------------- .4
27-33 --------------- .5
34-39 --------------- .6
40-45 --------------- .7
46-51 --------------- .8
52-57 --------------- .9
58-60 --------------- .0

Excel has trouble with time computations that span more than one day (e.g. 2345-0130) otherwise I would have already figured this problem out.

Through long drawn out in cell formulas I've got a manual solution that is close to working for me but also runs into problems given certain specific situations that span more than one day.

I figure there's got to be a solution using macros to do what I want, but unfortunately I don't have the programming knowledge to create working macros.

Can anybody provide help? I would be extremely grateful.

If there's a roundabout way of handling this that anybody can provide I'm open to those suggestions as well.

Thanks.

**DonkeyOte** · 11-05-2009, 03:44 AM

I think perhaps you mean:

C1: =24*MOD(TEXT(B1,"00\:00")-TEXT(A1,"00\:00"),1)

So if B1 were 2345 and A1 0130 then the above would generate 1.75
I will leave the rounding decision to you...
for ex. XL would round the above to 1.8 as opposed to 1.7 which would contradict your table

**criticalmass** · 11-07-2009, 03:08 AM

Excellent, thanks. Figuring out the rounding issue took a while but your formula got me to the point where I could program the remaining operations to suit my needs using VLOOKUP and a few reference cells.

Even after reading Excel help and a few explanations online I still don't understand how your formula works, but it does. I understand the MOD function but not so much how the "00\:00" formatting affects the results.

I'd like to understand how it works, but I'll settle for the simple fact that it does.

Thanks again.

**DonkeyOte** · 11-07-2009, 04:12 AM

Originally Posted by criticalmass

...how the "00\:00" formatting affects the results....I'd like to understand how it works...

Time values are as you know are generally speaking entered in format of hh:mm[:ss]

A true Time value in XL is a number, ie Noon = 0.5, 6am = 0.25, 6pm = 0.75 etc... 24 hours equates to 1.

Given we know 24 hours is 1 it follows that entering time less-colon, ie as a whole number, will in datetime terms be interpreted by XL as number of days eg

0600 --> seen as 600 days
1800 --> seen as 1800 days

In our case we need the : because that tells XL it's a time value and as a result it will be assigned an appropriate numerical value.

In our time calculations we thus use the TEXT function to take our whole number and convert it into a string in which we add a colon between the hours/minutes

TEXT(0600,"00\:00") --> "06:00"

At this point the above is still a text string and thus does not represent the true time value of 6am (0.25) ... however like any other "number stored as text" we can coerce the string to a number by applying a mathematical operator to it (addition, subtraction, division, multiplication etc...) ... eg

1*"06:00" --> the "06:00" time string is converted to time 06:00 and thus number 0.25

In this case given you wish to subtract one time from another we can use the act of subtraction to coerce both end & start time strings simultaneously

TEXT(B1,"00\:00")-TEXT(A1,"00\:00")

So using our example numbers

"18:00"-"06:00" --> 0.75 - 0.25 -> 0.5

I hope that helps.

The MOD as you say you understand - for the benefit on any others who may stumble here this is used as a way of handling possibility that time values cross midnight
(it won't work if total time between the two values is > 24 hours)

**mbarr** · 01-27-2011, 08:04 PM

I am trying to figure the number of hours certain rooms in certain bldgs are used. The data I have shows start and end times. So, in one event the meeting starts at 1240 ends at 1400 = 160 then I multiply it by the number of meetings per week for a total here (2*160) of 320. I have subtracted those times, but the format is military time. I am not sure how to get the total number of regular hours from the data I have created. Any ideas?

I sure would appreciate your help!

**shg** · 01-27-2011, 08:08 PM

Welcome to the forum.

Would you please take a few minutes to read the forum rules, and then start your own thread?

Thanks.

**joeballer10** · 06-20-2013, 03:23 PM

My question is so close to this problem that I won't create another thread.

I am trying just about the same process but I DO NOT need to convert to a number with decimals. I only need the calc done to show how many minutes elapsed between the times, say 2315 and 1755 ie. 320 (minutes don't need to display), not 560 which is just the whole numbers subtracted.

I am excited to have the pros on this thread respond. Thanks in advance.