Duration is calculated by subtraction (B-A).But In some cells I have A>B .Then the duration cant be calculated. I want to interchange the cell values where A>B.In roughly,If A>B then,A=B and B=A.otherwise The cell will be remain unchanged. How Can I do it in excel?

CodePudding user response：

Simply try below formula then apply desired date/time format to resulting cell.

=IF(A2>B2,A2-B2,B2-A2)

In case of only time you may use below formula to return times only.

 =IF(IF(A2>B2,A2-B2,B2-A2)>=1,TEXT(IF(A2>B2,A2-B2,B2-A2),"dd/mm/e hh:mm:ss"),TEXT(IF(A2>B2,A2-B2,B2-A2),"hh:mm:ss"))

CodePudding user response：

The simplest solution would be using ABS, like so:

=ABS(A2-B2)

So, regardless of the order, the duration will be positive.

The only thing left to do is format e.g. 0.99984 to a Time format. If you end up with a duration greater than 1, that means 24 hours (1 day).

So, you could also get the number of days and the hours by:

=ROUNDDOWN(C2;0) -- To get whole days

=MOD(C2;ROUNDDOWN(C2;0)) -- To get the time (Hours, Minutes, Seconds)

Hope this was helpful! ^_^