Subtracting times

[redfour]

Subtracting one time from another is not as straightforward as your tip suggests.

Subtracting 05:00hrs from 22:00hrs is simple, but subtract 22:00hrs from 05:00hrs!

Using the IF statement can get round this problem.

Assume cell B1 is to be deducted from A1 and the result to go in C1.

To overcome the problem you need to add 24hrs to the value of A1 if it is less than the value of B1.

The formula =IF(B1>A1,(A1+24-B1),A1-B1) solves the problem.

 

[barry houdini]

Hi redfour,

Is this a question or an answer?

To get the difference between two times, start time in A1 and end time in B1

=B1-A1+(A1>B1)

format as h:mm