Assume the following dates in range B4:B8
B4 – 29/09/2011
B5 – 04/10/2012
B6 – 05/10/2012
B7 – 07/10/2014
B8 – 15/07/2015
One may want to count dates which are within 365 days of each other. The answer should be four. The four dates are:
1. 04/10/2012
2. 05/10/2012
3. 07/10/2014
4. 15/07/2015
The formula for getting four is
=SUMPRODUCT(1*(B4:B8-B3:B7<=365))+SUMPRODUCT((B4:B8-B3:B7>365)*(B4:B8-B5:B9>=-365)*(B4:B8-B5:B9<=365))
Cell D4 has 365. This can be changed to any other positive number.
For this formula to work, the following two conditions should be satisfied:
1. Cell B3 should be left blank; and
2. Dates in range B4:B8 should be sorted in ascending order.
Count dates in a range which are within 365 days of each other
{ 4 Comments }