How to calculate total hours in a cell: Time in Military

[Airam]

Hi

Is it possible to calculate the total hours in a cell. I've seen some thread about this however the time format of my data is in military. Please see sample screenshot;

Thanks in advance

 

[AlphaFrog]

Cell C2 has 15:00-22:00

This will calculate the time span from that cell
=TIMEVALUE(RIGHT(C2,5))-TIMEVALUE(LEFT(C2,5))

Format the cell with this formula as time.

 

[Airam]

Hi AlphaFrog.. Thanks so much! Appreciate your help..

 

[Airam]

Hi

A follow up question:

I tried adding up the hours for all days but I am encountering an error when a time/shift crosses over midnight.

Here's the formula I used:

=IF(B2="OFF","0",TIMEVALUE(RIGHT(B2,5))-TIMEVALUE(LEFT(B2,5)))+IF(C2="OFF","0",TIMEVALUE(RIGHT(C2,5))-TIMEVALUE(LEFT(C2,5)))+IF(D2="OFF","0",TIMEVALUE(RIGHT(D2,5))-TIMEVALUE(LEFT(D2,5)))+IF(E2="OFF","0",TIMEVALUE(RIGHT(E2,5))-TIMEVALUE(LEFT(E2,5)))+IF(F2="OFF","0",TIMEVALUE(RIGHT(F2,5))-TIMEVALUE(LEFT(F2,5)))+IF(G2="OFF","0",TIMEVALUE(RIGHT(G2,5))-TIMEVALUE(LEFT(G2,5)))+IF(H2="OFF","0",TIMEVALUE(RIGHT(H2,5))-TIMEVALUE(LEFT(H2,5)))

But when I changed the 00:00 time in Cell B4 & H4 to 23:59, I was able to get a result;

Please advice.

 

[AlphaFrog]

The solution to this is two steps.
1.) Turn on the option "Use 1904 Date System" that allows Excel to calculate negative time without error.
2.) Use the ABS function in the formulas to convert negative time back into positive time.

For Excel 2007:

• Click on the Office icon in the upper left
• Click the Excel Options button
• Click Advanced on the left side panel of the dialog.
• Scroll down to the section "When Calculating This Workbook"
• Check the "Use 1904 Date System" check box
• OK

Cell B4 has 16:00-00:00

This will calculate the time span from that cell
=ABS(TIMEVALUE(RIGHT(B4,5))-TIMEVALUE(LEFT(B4,5)))

The ABS function (Absolute) will strip the negative sign from any negative values.

 

[Brian from Maui]

Be a lot easier if you had a column for both start and end times, with total column

See

http://www.cpearson.com/Excel/datetime.htm#AddingTimes

 

[AlphaFrog]

Cell B4 has 16:00-00:00

This will calculate the time span from that cell
=ABS(TIMEVALUE(RIGHT(B4,5))-TIMEVALUE(LEFT(B4,5)))

The ABS function (Absolute) will strip the negative sign from any negative values.

Correction:
Everything I previously said is junk. Just use this formula. You don't have to turn on the 1904 Date System option.

Cell B4 has 16:00-00:00

This will calculate the time span from that cell
=MOD(1+TIMEVALUE(RIGHT(B4,5))-TIMEVALUE(LEFT(B4,5)),1)

 

[Airam]

Hi Brian from Maui - Thanks for the info.. I might try that some other time..

Hi AlphaFrog - That works! Thanks so much!