Time formula

jgneely72151

Can anyone assist with writing a formula using military hours? See example sheet fields below.

Shimanta Roy

Add a helper column for calculating the duration time in decimal. Name the column as Duration. Now apply the following formula in the Duration column:

=((VALUE(LEFT([Time Out]@row, FIND(":", [Time Out]@row) - 1)) + VALUE(RIGHT([Time Out]@row, 2)) / 60) + ([Date Ended]@row - [Date Started]@row) * 24) - (VALUE(LEFT([Time In]@row, FIND(":", [Time In]@row) - 1)) + VALUE(RIGHT([Time In]@row, 2)) / 60)

Now use the following formula in your Total Time column:

=INT(Duration@row) + ":" + IF((Duration@row - INT(Duration@row)) * 60 < 10, "0") + (Duration@row - INT(Duration@row)) * 60

Shimanta Roy

Add a helper column for calculating the duration time in decimal. Name the column as Duration. Now apply the following formula in the Duration column:

=((VALUE(LEFT([Time Out]@row, FIND(":", [Time Out]@row) - 1)) + VALUE(RIGHT([Time Out]@row, 2)) / 60) + ([Date Ended]@row - [Date Started]@row) * 24) - (VALUE(LEFT([Time In]@row, FIND(":", [Time In]@row) - 1)) + VALUE(RIGHT([Time In]@row, 2)) / 60)

Now use the following formula in your Total Time column:

=INT(Duration@row) + ":" + IF((Duration@row - INT(Duration@row)) * 60 < 10, "0") + (Duration@row - INT(Duration@row)) * 60