repetitive time intervals

Originally Posted by joeljoel

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

Originally Posted by joeljoel

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.

Originally Posted by 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?

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.