Search for groups of cells that contain values, calculate the difference, place result in adjacent column

Not sure how to approach this problem. I have a column (column A) with a list of times. The column has a lot of inconsistencies such that not all rows contain numerical values.

What I need to do is calculate the difference between the times (rows) that do contain numerical values. And I need the place result adjacent (column B) to the first value of that specific grouping of rows.

The attached photo paints a better picture explaining what I need to do. Example Data

There is no pattern to the data. So I need excel to search the column, find the groupings, subtract the first value from last value in a group of times, and place the result in column B.

Thanks for any suggestions!

Solution

This may appear to overuse Excel's let function, but it can improve performance considerably by minimizing the calculations needed.

Throw this in cell B4 and drag down and it should give you what you want.

=IF(ISNUMBER(A4),IF(NOT(ISNUMBER(A3)),LET(
    aColumn,A:A,
    lastUsedRow,MATCH(2,1/(aColumn<>""),1),
    CleanRange,INDEX(aColumn,ROW(),1):INDEX(aColumn,lastUsedRow,1),
    endRow,IFERROR(MIN(FILTER(ROW(CleanRange),IF(NOT(ISNUMBER(CleanRange)),ROW(CleanRange)>ROW(),FALSE)))-1,lastUsedRow),
    INDEX(aColumn, endRow,1)-INDEX(aColumn,ROW(),1)),""),"")

You could also improve this by having a named Range of lastUsedRow, =MATCH(2,1/($A:$A<>""),1) in the worksheet and then removing that line in the formula so it doesn't have to keep recalculating it.