How do I caculate 24 hour military times

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tom - 29 Oct 2006 04:40 GMT

How can I caculate military times such as 1800 hours to 400 hours for a time
sheet?

Reply to this Message

RS - 29 Oct 2006 07:04 GMT

Hi Tom,

Two steps: 1) Enter time with a colon separating the hours and minutes
(18:00 or 4:00), and 2) Format the cells in military time {Press Ctrl 1 or go
to the Format Menu -> Cells -> Number tab -> Time [select the military format
you want (ex: 13:00) -> press OK]}. Hope this helps.

> How can I caculate military times such as 1800 hours to 400 hours for a time
> sheet?

Reply to this Message

Bob Phillips - 29 Oct 2006 11:40 GMT

=B21-A21+(B21<A21)*2400

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> How can I caculate military times such as 1800 hours to 400 hours for a time
> sheet?

Reply to this Message

Epinn - 30 Oct 2006 04:58 GMT

Bob,

I should learn how to use Boolean instead of IF( ). Case in point +(B21<A21)*.....

If B21 = A21, there is no way to tell if it is the next day or the same day. I guess depending on the user's need, one may have to change the formula to

=B21-A21+(B21<=A21)*2400 to reflect 2400 for the lapse of one day.

Any comments?

Epinn

=B21-A21+(B21<A21)*2400

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> How can I caculate military times such as 1800 hours to 400 hours for a

time

> sheet?

Reply to this Message

Bob Phillips - 30 Oct 2006 09:55 GMT

Bob,

> I should learn how to use Boolean instead of IF( ). Case in point +(B21<A21)*.....

> If B21 = A21, there is no way to tell if it is the next day or the same day.
> I guess depending on the user's need, one may have to change the formula to

> =B21-A21+(B21<=A21)*2400 to reflect 2400 for the lapse of one day.

The formula already has an algorithm to determine whether it goes over
midnight, that is whan the +(B21<=A21)*2400 is doing, it determines that it
goes over midnight if the end time is earlier than the start time, on the
basis that the task will not be greater than 24 hours. I suppose you could
argue he might start at 8:00 and finish at 8:00.

Reply to this Message

Lori - 30 Oct 2006 11:07 GMT

=MOD(B1-A1,2400)

> How can I caculate military times such as 1800 hours to 400 hours for a time
> sheet?

Reply to this Message

Lori - 30 Oct 2006 12:03 GMT

The above fomulae (Bob's and mine) only work for hourly times as in the
example given. But for other times an adjustment is needed eg 2115 to
800 should give 1045 (not 1085). This should work for all times entered
in "hhmm" format:

=--TEXT(MOD(TEXT(B1,"0\:00")-TEXT(A1,"0\:00"),1),"hhmm")

Reply to this Message