I wanted to calculate the difference of numbers that are inputted in a Google sheet row like this:

Row 1: 35 | 37 | 39 | 38 and So on (until last non-empty cell)...

And the output would be:

Row 1 Output: (37-35) (39-37) (38-39) = (2 2-1)=3

the suggested formula for this calculation is:

=LAMBDA(rg,SUMPRODUCT(rg-OFFSET(rg,0,-1)))(INDEX(1:1,2):INDEX(1:1,COUNTA(1:1)))

Taking forward to this concept, now I want to calculate days between dates instead of numbers.

something like this (date format:yyy-mm-dd):

Row 1: (blank cell) | 2022-07-06 | (blank cell) | 2022-07-07 | 2022-07-08 and So on (until last non-empty cell)...

Expected Row 1 Output:(2022-07-07-2022-07-06) (2022-07-08-2022-07-07) = (1 1)=2

I used the above formula but I reckon formula is producing incorrect output because it does not exclude blank cells in between these dates, that's why it gives 44749 an an output,image is also attached:

Here is the how the table looks like in sample sheet in case you want to test it.

Table is starting from column A to Column G. Any guidance would be much appreciated, thank you.

CodePudding user response：

There are many ways of finding the first and last non-blank cell in a row, but let's use xlookup for the sake of argument:

=ArrayFormula(xlookup(true,(B2:2<>""),B2:2,,0,-1)-xlookup(true,(B2:2<>""),B2:2,,0, 1))

But people just don't believe me when I tell them that (G2-E2) (E2-D2) (D2-B2) simplifies to G2-B2. It's basic algebra folks!

Or this is equivalent to my original formula but doesn't lend itself to being written as an array formula:

=index(filter(B2:2,B2:2<>""),count(B2:2))-index(filter(B2:2,B2:2<>""),1)

EDIT

Possible array formula (but could be a bit inefficient):

=iferror(byrow(B2:Z,lambda(r,index(filter(r,r<>""),count(r))-index(filter(r,r<>""),1))))