Rolling 365 days formula

Steve_KSA

I need some help in setting up formula which returns the current # days visiting a country, in the past rolling 365 days, across a group of 2 people. All data is related to 1 country only.

Input Columns would be Persons Name; Arrival Date into country; Departure Date out of country.

Output calculation would be:

1. # of days in country in past rolling 365 days (from today)
2. # of days in country in rolling 365 days after taking consideration of next visit (next arrival & departure date)

Thank you

James Keuning

I can get you part of the way there. For the future calculation, you only want to count the NEXT trip? So if there are two future trips, only count the first? What if they are on a trip right now. Do the remaining days in the country count as the future trip? Or do we look for an arrival date later than today to define a future trip?

To calculate the dates including the future dates, you can use this:

=IF(depart@row > TODAY() - 365, depart@row - IF(arrive@row < TODAY() - 365, TODAY() - 365, arrive@row), "entirely in past")

To modify this so that the next future trip is included, you can use this:

=IF(AND(arrive@row > TODAY(), depart@row > TODAY()), IF(arrive@row + who@row = MIN(COLLECT([arrive in future]:[arrive in future], who:who, who@row)) + who@row, depart@row - arrive@row, "future but not next"), IF(AND(NOT(arrive@row > TODAY()), depart@row > TODAY()), IF(depart@row > TODAY(), TODAY(), depart@row) - IF(arrive@row > TODAY(), TODAY(), arrive@row), IF(depart@row > TODAY() - 365, depart@row - IF(arrive@row < TODAY() - 365, TODAY() - 365, arrive@row), "entirely in past")))

This requires you to create a helper column, "arrive in future" with this formula: =IF(arrive@row > TODAY(), arrive@row, "")

I have the "entirely in past" and "future but not next" as error handling, you can change those to 0 or "".

EDIT -- OOPS I forgot something! For the future trips I need to recalculate the 365 day look back based on the future date, not based on today's date. That might be tricky. I suppose we need to calculate the 365 look back based in the departure date of the next future trip? So that we are only counting days that fall within 365 days prior to that departure date.

EDIT 2 - thinking about this, but not working on it, the calculation considering the future trip may need to recalculate the today()-365 for every trip for that person. In other words, if the person is not in the country today, and they just returned from a 100 day trip, and they are planning to go to the country again in 360 days for a ten day trip, should the result for that person be 5? Because on the day they leave for the trip, their days count will be 5, but on the day they return, the days count will be 10.

Steve_KSA

Hi James, really appreciate your efforts with this.

I will give it a run and see how I go

To start with, I think I will need to leave it reasonably basic, looking at single future trips.

Many thanks

Steve