+ Reply to Thread
Results 1 to 5 of 5

Military time range to decimal of hour

  1. #1
    Registered User
    Join Date
    04-02-2008
    Posts
    8

    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
    Last edited by slopland; 03-31-2010 at 09:25 PM.

  2. #2
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,802

    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. #3
    Registered User
    Join Date
    04-02-2008
    Posts
    8

    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. #4
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,802

    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. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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