# 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

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

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.

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)

Glad I could help! Please mark the thread as solved by clicking Edit in your first post, then Go Advanced, then change the Prefix drop-down to Solved.

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")))

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1