# Military time range to decimal of hour

1. ## Military time range to decimal of hour

OK> I have searched but not sure I am seeing what I need or understanding it.
Simply stated I have a multiple column client tracker with a column for time in military time, then I have a total hours column. I need to know how to calculate the total hours column simply by putting in the time.

Time (column B) Hours (column G)
1500-1545 want this to calculate .75
1600-1630 want this to calculate .50

I have tried a few things but I get zero since excel sees the time range as 1500 minus 1545 etc.

TIA  Register To Reply

2. ## Re: Military time range to decimal of hour

Hi Slopland, perhaps try:

=(TIMEVALUE(MID(B1,6,2)&":"&RIGHT(B1,2))-TIMEVALUE(LEFT(B1,2)&":"&MID(B1,3,2)))*24

This assumes your time format in column B is always xxxx-yyyy  Register To Reply

3. ## Re: Military time range to decimal of hour

You are the man.. That worked perfect.

Not to be picky, but if the B column is blank which somtimes happens when I dont see enough clients to fill that column, it shows VALUE!! in the hour column. Anyway to have the hour column to show blank or zero if no time is entered?

But, thank you very much.. That is awsome.  Register To Reply

4. ## Re: Military time range to decimal of hour

You can wrap the whole function in an IF to see if Bx is blank, for example:

=IF(B1="","",(TIMEVALUE(MID(B1,6,2)&":"&RIGHT(B1,2))-TIMEVALUE(LEFT(B1,2)&":"&MID(B1,3,2)))*24)  Register To Reply

5. ## Re: Military time range to decimal of hour

For no reason other than to demonstrate a technique used by daddylonglegs (our resident date guru) you could also use TEXT which would be slightly shorter:

=IF(B1="","",24*(TEXT(RIGHT(B1,4),"00\:00")-TEXT(LEFT(B1,4),"00\:00")))  Register To Reply