Skipping blank cells when calculating time difference
 

In A1, 1:30
.... A2, 1:45
.... A3, 2:30
.... A4 is blank
.... A5 is blank
.... A6, 3:30
I would like a formula to calculate the time difference in one column, while
skipping the empty cells.
Example, calculate the difference between A6 & A3, then A3 & A2.
It would only need to go to the next time up the column, but that may be 5
cells up.
Thanks.

Skipping blank cells when calculating time difference
 

With Time values beginning in cell A1, blank cells interspersed

Try something like this:
B2: =IF(A2="","",A2-LOOKUP(10^99,A$1:A1))
Copy that formula down as far as you need.
Format those cells as Time

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"GARDNERGUY" wrote:

In A1, 1:30
... A2, 1:45
... A3, 2:30
... A4 is blank
... A5 is blank
... A6, 3:30
I would like a formula to calculate the time difference in one column, while
skipping the empty cells.
Example, calculate the difference between A6 & A3, then A3 & A2.
It would only need to go to the next time up the column, but that may be 5
cells up.
Thanks.

Skipping blank cells when calculating time difference
 

here is a function that will do the trick

=timediff(A4,A6)
A4 will move up to A3

Function timediff(time1 As Range, time2 As Range) As Single

If IsEmpty(time1) Then
t1 = Cells(time1.Row, time1.Column).End(xlUp)
Else
t1 = time1
End If
If IsEmpty(time2) Then
t2 = Cells(time2.Row, time2.Column).End(xlUp).Row
Else
t2 = time2
End If
timediff = t2 - t1
End Function

"GARDNERGUY" wrote:

In A1, 1:30
... A2, 1:45
... A3, 2:30
... A4 is blank
... A5 is blank
... A6, 3:30
I would like a formula to calculate the time difference in one column, while
skipping the empty cells.
Example, calculate the difference between A6 & A3, then A3 & A2.
It would only need to go to the next time up the column, but that may be 5
cells up.
Thanks.

Skipping blank cells when calculating time difference
 

Worked great!


"Ron Coderre" wrote:

With Time values beginning in cell A1, blank cells interspersed

Try something like this:
B2: =IF(A2="","",A2-LOOKUP(10^99,A$1:A1))
Copy that formula down as far as you need.
Format those cells as Time

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"GARDNERGUY" wrote:

In A1, 1:30
... A2, 1:45
... A3, 2:30
... A4 is blank
... A5 is blank
... A6, 3:30
I would like a formula to calculate the time difference in one column, while
skipping the empty cells.
Example, calculate the difference between A6 & A3, then A3 & A2.
It would only need to go to the next time up the column, but that may be 5
cells up.
Thanks.