Working with military times

[Livnlife]

I am trying to make a spread sheet that will calculate the total time that an item is used. I am using military time and have the following formula for the calculation
=TIMEVALUE(TEXT(B1,"00\:00"))-(TEXT(A1,"00\:00"))
where A1 is the start time and B1 is the end time. The times are put in as 4 digits military time (hourminute without a . This works great until the end time(B1) is past midnight such as 0100.
Here is an example:
A1 B1 C1
1300 1530 2:30

A1 B1 C1
2300 0200 #######

Message is that it puts #### if the value is negative.

Any ideas?

 

[T. Valko]

I am trying to make a spread sheet that will calculate the total time that an item is used. I am using military time and have the following formula for the calculation
=TIMEVALUE(TEXT(B1,"00\:00"))-(TEXT(A1,"00\:00"))
where A1 is the start time and B1 is the end time. The times are put in as 4 digits military time (hourminute without a . This works great until the end time(B1) is past midnight such as 0100.
Here is an example:
A1 B1 C1
1300 1530 2:30

A1 B1 C1
2300 0200 #######

Message is that it puts #### if the value is negative.

Any ideas?

Try this...

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

Format as [h]:mm

 

[Aladin Akyurek]

I am trying to make a spread sheet that will calculate the total time that an item is used. I am using military time and have the following formula for the calculation
=TIMEVALUE(TEXT(B1,"00\:00"))-(TEXT(A1,"00\:00"))
where A1 is the start time and B1 is the end time. The times are put in as 4 digits military time (hourminute without a . This works great until the end time(B1) is past midnight such as 0100.
Here is an example:
A1 B1 C1
1300 1530 2:30

A1 B1 C1
2300 0200 #######

Message is that it puts #### if the value is negative.

Any ideas?

Something like:

=TEXT(B1,"00\:00")-TEXT(A1,"00\:00") + (TEXT(B1,"00\:00")+0 < TEXT(A1,"00\:00")+0)

 

[Livnlife]

Something like:

=TEXT(B1,"00\:00")-TEXT(A1,"00\:00") + (TEXT(B1,"00\:00")+0 < TEXT(A1,"00\:00")+0)

Thanks so much. It works!!!

 

[Aladin Akyurek]

Thanks so much. It works!!!

You are welcome. Thanks for providing feedback.