I have a duration in one cell dd hh:mm:ss: 15 11:02:23 I need to substract 14 23:06:40

from it?

Does anyone know how to do this in excel?Thanks for your help.

Agi

message edited by Agi

Perhaps if you started here... http://www.cpearson.com/excel/datet...

...and worked your way to here:

http://www.cpearson.com/excel/datea...

...you'd find a way to accomplish your goal.

Let us know if you get stuck.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Probably the simplest way would be to separate the Days from the Hours then do the math, so you data would look like: A B 1) Days Hours 2) 15 11:02:23 3) 14 23:06:40Then in cell B4 enter the formula:

=IF(B2<B3,(B2+1)-B3,B2-B3)

We first have to check to make sure the beginning hour, in cell B2,

is greater then the ending hour, in cell B3,

if it is not, then we need to add 1 day to the beginning hour.

Then do the subtraction.The formula for cell A4, the Days would be:

=IF(B2<B3,(A2-1)-A3,A2-A3)

Again, we first check the hours to make sure the beginning hour is

greater then the endng hour, and if not, we have to subtract 1 day

from our beginning day figure.

Then do the subtraction.Having both the Days and Hours in one cell only makes matter more difficult.

There probably is a very long and complicted formula you could write

using MID and FIND to cut up the strings and then do the math, but by

separating them we can do it with two relatively simple formulas.MIKE

Ask Your Question

Weekly Poll

Do you think Europe's new data protection laws will affect you?

Discuss in The Lounge

Poll History