Skipping blank cells when calculating time difference

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Worked great!

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