@ > Home > Contents > Calculate hours without days Excel 4+, SpreadCE, Ooo, Lotus

Task and Solution: (Thomas Ramel)

To get only hours (date-time serial numbers 0-1), compute the change B1: in relation to the existing time A1: as follows:

A2: =MOD(A1+B1,1) when added (or subtracted by negative B1:)
A2: =MOD(A1-B1,1) when subtracted; note: if B1>A1, it will not result in a negative time.
C1: =MOD(B1-A1,1) here B1 is a later time. If A1>B1, it will not result in a negative time.

The benefits compared with

A2: =A1+B1-(A1+B1>1)
A2: =(B1>A1)+A1-B1
C1: =(B1<A1)+B1-A1 

are:

1. The result is always between 0 and 1 (no days attached)
2. Single use of arguments: Can be useful in cases of complicate pre-calculation

Do not forget to format as hh-mm!