# Thread: Need Help With Military Time Formula (Excel 2003)

1. ## 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. ## 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

3. ## 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. ## 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. ## 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
•