Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Norwich, Connecticut, USA
    Posts
    150
    Thanks
    14
    Thanked 0 Times in 0 Posts

    Need Help With Military Time Formula (Excel 2003)

    I keep a log for medical reasons of what time I go to bed each night. The time must be entered in military time (i.e., 0135). I need a way to calculate / update each day the average time that I go to bed. Would appreciate any help in solving this problem. Thanks. Robert

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help With Military Time Formula (Excel 2003)

    Robert,
    I've attached a very simple spreadsheet in Excel that should do what you're looking for.

    Hope it helps!
    Jeremy
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Help With Military Time Formula (Excel 2003)

    Say that you enter the times in B2:B100; the cells are formatted as 0000.

    In C2, enter the formula
    <code>
    =IF(ISBLANK(B2),"",MOD(B2,100)/1440+TRUNC(B2/100)/24)
    </code>
    And fill down to C100. You can format C2:C100 as times, if you like, but it isn't necessary. The formula:
    <code>
    =AVERAGE(C2:C100)
    </code>
    calculates the average; the cell containing this formula must be formatted as time, or as hhmm if you want to display it as military time. You can hide column C if you like.

    If you prefer to omit the intermediary formulas, use
    <code>
    =AVERAGE(IF(ISBLANK(B2:B100),"",MOD(B2:B100,100)/1440+TRUNC(B2:B100/100)/24))
    </code>
    entered as an array formula, i.e. confirmed with Ctrl+Shift+Enter. Again, the cell containing the formula must be formatted with a time format or as hhmm.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Norwich, Connecticut, USA
    Posts
    150
    Thanks
    14
    Thanked 0 Times in 0 Posts

    Re: Need Help With Military Time Formula (Excel 2003)

    Hans,
    Thanks for your reply. I followed your suggestions exactly but still have a problem. In column B I enter the time in military format (2200,2330, etc) and your formulas work great until I enter a time after midnight (i.e., 0130) in column B, and then the setup no longer works

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Help With Military Time Formula (Excel 2003)

    2200 will become 22:00 and 0100 will become 01:00. Excel has no way of knowing whether you mean 01:00 on the same day as the 22:00 or on the next day. So the formula must be modified:

    =IF(ISBLANK(B2),"",MOD(B2,100)/1440+TRUNC(B2/100)/24+(TRUNC(B2/100)<12))

Posting Permissions

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