Military time range to decimal of hour

**slopland** · 03-31-2010, 12:31 AM

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

**Paul** · 03-31-2010, 12:42 AM

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

**slopland** · 03-31-2010, 12:56 AM

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.

**Paul** · 03-31-2010, 01:00 AM

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.

**DonkeyOte** · 03-31-2010, 03:25 AM

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