repetitive time intervals

[joeljoel]

If I have a formula to test if the dates meet a certain condition such as this:

=AND($A5>=DATE(2018,3,1),$A5<=DATE(2019,9,30))*1

How can I modify this to test the same time frame for years of specified intervals, such as every ten years or every 4 years??

I could just add in those years for the next 20 or 50 or 100 years, but I think there must be a better, more efficient, way, right?

Thank you!!

[DonkeyOte]

Given the spans exceed 1 year I'm not sure if there's an elegant approach or not - there might well be - in the meantime - a less efficient sledge hammer approach might be:

=SUMPRODUCT((A5>=EDATE($C5,12*$B5*(ROW($A$1:$A$20)-1)))*(A5<=EDATE($D5,12*$B5*(ROW($A$1:$A$20)-1))))

where A5 holds original date, B5 the year interval (4, 10 etc), C5 & D5 the start and end points of the original interval (eg 1-Mar-2018 to 30-Sep-2019)

the above caters for 20 * interval ... you could choose to make that dynamic also based on the interval value [eg via LOOKUP construct] - such that the smaller the interval the greater the repetition (2 year = 50 repetitions for ex.)

[joeljoel]

Thanks, DonkeyOte. I was thinking there was a simpler way to do this.

In any event, before I give this a try, what does the $A$1:$A$20 refer to?

Thanks!

[DonkeyOte]

what does the $A$1:$A$20 refer to?

It's used to create an Array of values from 1 through 20.

This is what I meant when I referred to the formula handling up to 20 intervals of a given period - if you wanted more/less adjust 20
(or as implied consider using a variable based on the no. Years between the intervals - the higher the no. the lower the instances required [presumably])

I was thinking there was a simpler way to do this.

I'm sure there's a more elegant approach available but above my pay grade I suspect... were interval duration never to exceed 12 months then things become simpler but the ex. implies otherwise.

[joeljoel]

So if I understand correctly, I could replace ROW($A$1:$A$20) with ROW(20) and my interval from B5 would repeat 20 times, or 80 years in the case of 4 year intervals?

I think you understood correctly. I am trying to determine if my date in A5 will be between 3/1/2018 and 9/30/2019 or 3/1/2028 and 9/30/2029 or 3/1/2038 and 9/30/2039 etc etc.

[DonkeyOte]

So if I understand correctly, I could replace ROW($A$1:$A$20) with ROW(20) and my interval from B5 would repeat 20 times, or 80 years in the case of 4 year intervals?

The $A$20 in ROW($A$1:$A$20) determines the upper bound of the number of interval dates generated.
If you wanted 30 repeats you would change $A$20 to $A$30, for just 5 repeats you would use $A$5.

ROW generates an Array of values so ROW($A$1:$A$5) -> {1;2;3;4;5} etc...

In essence we're simply creating n pairs of interval start/end dates where n is determined by the upper ROW references as detailed above and the dates themselves increment from the previous date by the number of years specified.
We then compare the various pairs to see if any given pair covers our actual date.