CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
EagleEye
EagleEye

Reputation: 510

Calculate Days between series of dates in a range

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:

days issue

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

Desired Output (No. of Days) Date1 Date2 Date3 Date4 Date5 Date6
3 2022-07-06 2022-07-07 2022-07-08 2022-07-09
(G2-E2)+(E2-D2)+(D2-B2)
1 2022-07-06 2022-07-07
(G4-C4)

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

Upvotes: 0

Views: 414

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34355

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))

enter image description here

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))))

Also if dates are in ascending order left to right (or more exactly, if the first date is the smallest and the last date the largest), and because min and max ignore blanks, you can simplify the original formula to:

=max(B2:2)-min(B2:2)

or with byrow:

=ArrayFormula(byrow(B2:Z,lambda(r,if(min(r)=0,,max(r)-min(r)))))

Upvotes: 4

Related Questions