Sorry if this has been covered elsewhere - couldn't think of of a search
keyword!

I'm working on rota. I've got a row for each hour, and a column for each
duty. In each column there are two numbers: a start time and a finish time.
But, of course, they won't be in the same rows.

I want to try out different possibilities and show the total time (start
time subtracted from finish time) under each column, without having to
specify the 2 cells every time I try a different arrangement.

Is there a way to do this? I should add that I'm not an expert coder, but
happy to cut and paste.

Chris,

Why not just subtract. Max time - Min time

=MAX(C2:C10)-MIN(C2:C10)

Mike

"Chris Hughes" wrote:

Sorry if this has been covered elsewhere - couldn't think of of a search
keyword!

I'm working on rota. I've got a row for each hour, and a column for each
duty. In each column there are two numbers: a start time and a finish time.
But, of course, they won't be in the same rows.

I want to try out different possibilities and show the total time (start
time subtracted from finish time) under each column, without having to
specify the 2 cells every time I try a different arrangement.

Is there a way to do this? I should add that I'm not an expert coder, but
happy to cut and paste.

Hi Chris

Try the below formula which substracts the last entry from the first entry.
Adjust the defined range B2:B10 to suit your requirement.

=LOOKUP(10^10,B2:B10)-INDEX(B2:B10,MATCH(TRUE,INDEX(ISNUMBER(B2:B10),),) )

'Format the formula cell to excel time format..

If this post helps click Yes
---------------
Jacob Skaria


"Chris Hughes" wrote:

Sorry if this has been covered elsewhere - couldn't think of of a search
keyword!

I'm working on rota. I've got a row for each hour, and a column for each
duty. In each column there are two numbers: a start time and a finish time.
But, of course, they won't be in the same rows.

I want to try out different possibilities and show the total time (start
time subtracted from finish time) under each column, without having to
specify the 2 cells every time I try a different arrangement.

Is there a way to do this? I should add that I'm not an expert coder, but
happy to cut and paste.

Thanks Mike and Jacob - 2 great solutions!