Formula to track cumulative days away within a rolling 365 day period-CodePudding

As per the following example, example my known variables are my departure and return dates to a country. The 'days' column shows the number of days away on that specific trip, given by =(Return Date Cell - Departure Date Cell).

The column I am trying to populate is the number of days within the past 365 day period that I have been away from the country. This would be calculated using the return date, however it should be exclusive of the return and departure dates as those days count as being in the country.

The problem I'm struggling with when coming up with a formula in google sheets is it needs to accumulate all the days outside the country from the present day into the past, but it also needs to know when to 'stop counting' because it's out of that 365 day period.

In the example above, for row 4 (returns on 27/03/2022), it should not count the number of days from row 1 because the return date for row 1 was before 27/03/2021 (i.e. 365 days before).

Any ideas what I can do?

CodePudding user response：

This is the main formula you need to calculate the days of abstinence within the last 365 days.

IF(C2 < (TODAY() - 365); 0; IF(A2 >= TODAY() - 365; C2-A2; C2-TODAY()-365))

Explanation:

return date is before today - 365 days? set the value to 0.
- YES: set value to 0
- NO: Check departure is later than today - 365 days?
  - YES: The whole abstinence was within the last 365 days. Set value to return date - departure date.
  - NO: Only parts of the abstinence are within the last 365 days. Set value to today - 365 days - return date.

Apply this formula to every row and then on the bottom of your table sum the whole column using SUM() and you will have the days of abstinence in the last 365 days.

CodePudding user response：

cell E2:

=INDEX(IFERROR(1/(1/DAYS(C2:C, A2:A))))

cell F2:

count days (within last 365 days since today)

=INDEX(IFERROR(1/(1/LEN(SUBSTITUTE(FLATTEN(QUERY(TRANSPOSE(IF(IF(DAYS(C2:C, A2:A)>=
 SEQUENCE(1, 500, ), A2:A SEQUENCE(1, 500, ), )>=
 TODAY()-365, 1, )),,9^9)), " ", )))))

or if you don't count returnees

=INDEX(IFERROR(1/(1/LEN(SUBSTITUTE(FLATTEN(QUERY(TRANSPOSE(IF(IF(DAYS(C2:C, A2:A)>
 SEQUENCE(1, 500, ), A2:A SEQUENCE(1, 500, ), )>=
 TODAY()-365, 1, )),,9^9)), " ", )))))

cumulative days (within last 365 days since today)

=ARRAYFORMULA(MMULT(TRANSPOSE((SEQUENCE(COUNTA(C2:C))<=
 SEQUENCE(1, COUNTA(C2:C)))*LEN(SUBSTITUTE(FLATTEN(QUERY(TRANSPOSE(IF(IF(
 DAYS(INDIRECT("C2:C"&COUNTA(C2:C) 1), INDIRECT("A2:A"&COUNTA(C2:C) 1))>
 SEQUENCE(1, 500, ), A2:A SEQUENCE(1, 500, ), )>=
 TODAY()-365, 1, )),,9^9)), " ", ))), SEQUENCE(COUNTA(C2:C), 1, 1, 0)))

cumulative days (within last 365 days since latest return)

=ARRAYFORMULA(MMULT(TRANSPOSE((SEQUENCE(COUNTA(C2:C))<=
 SEQUENCE(1, COUNTA(C2:C)))*LEN(SUBSTITUTE(FLATTEN(QUERY(TRANSPOSE(IF(IF(
 DAYS(INDIRECT("C2:C"&COUNTA(C2:C) 1), INDIRECT("A2:A"&COUNTA(C2:C) 1))>
 SEQUENCE(1, 500, ), A2:A SEQUENCE(1, 500, ), )>=
 MAX(C2:C)-365, 1, )),,9^9)), " ", ))), SEQUENCE(COUNTA(C2:C), 1, 1, 0)))