Identifying dates that falls in the same month, day period across years

Question

So I've got a whole list of dates. I need to find out which dates fall within the same range (day and month) as my stipulated "Start Date" and "End Date" for each of the years.

For e.g. for Start Date:2/12/2004, End Date: 31/1/2005, dates falling between 2/12/2000 and 31/1/2001, 2/12/2001 and 31/1/2002, 2/12/2001 and 31/1/2002 etc should be flagged out as "Valid".

Currently I use an upper/lower limit and then test if the Date falling within the range.

Excel Formulas:

Upper: =DATE(YEAR(Date),MONTH(Test),DAY(Test)+Look Back Period)

Lower: =DATE(YEAR(Date),MONTH(Test),DAY(Test)-Look Back Period)

Valid: =IF(AND(Date<=Upper,Date>=Lower),1,0)

Test                    1/1/2005
Look Back Period (Days) 30
Start Date              2/12/2004 (=Test + Look Back Period)
End Date                31/1/2005 (=Test - Look Back Period)


    Date        Upper       Lower       Valid
    04/01/2000  31/1/2000   2/12/1999   1
    05/01/2000  31/1/2000   2/12/1999   1
    06/01/2000  31/1/2000   2/12/1999   1
    07/01/2000  31/1/2000   2/12/1999   1
    10/01/2000  31/1/2000   2/12/1999   1
    11/01/2000  31/1/2000   2/12/1999   1
    12/01/2000  31/1/2000   2/12/1999   1
    13/01/2000  31/1/2000   2/12/1999   1
    14/01/2000  31/1/2000   2/12/1999   1
    17/01/2000  31/1/2000   2/12/1999   1
    18/01/2000  31/1/2000   2/12/1999   1
    19/01/2000  31/1/2000   2/12/1999   1
    20/01/2000  31/1/2000   2/12/1999   1
    21/01/2000  31/1/2000   2/12/1999   1
    24/01/2000  31/1/2000   2/12/1999   1
    25/01/2000  31/1/2000   2/12/1999   1
    26/01/2000  31/1/2000   2/12/1999   1
    27/01/2000  31/1/2000   2/12/1999   1
    28/01/2000  31/1/2000   2/12/1999   1
    31/01/2000  31/1/2000   2/12/1999   1
    01/02/2000  31/1/2000   2/12/1999   0
    02/02/2000  31/1/2000   2/12/1999   0
    03/02/2000  31/1/2000   2/12/1999   0
    04/02/2000  31/1/2000   2/12/1999   0
    07/02/2000  31/1/2000   2/12/1999   0
    .
    .
    .
    30/11/2000  31/1/2000   2/12/1999   0
    01/12/2000  31/1/2000   2/12/1999   0
    04/12/2000  31/1/2000   2/12/1999   0 <-- :(
    05/12/2000  31/1/2000   2/12/1999   0 <-- :(
    06/12/2000  31/1/2000   2/12/1999   0 <-- :(
    07/12/2000  31/1/2000   2/12/1999   0 <-- :(
    08/12/2000  31/1/2000   2/12/1999   0 <-- :(
    11/12/2000  31/1/2000   2/12/1999   0 <-- :(
    12/12/2000  31/1/2000   2/12/1999   0 <-- :(
    13/12/2000  31/1/2000   2/12/1999   0 <-- :(
    14/12/2000  31/1/2000   2/12/1999   0 <-- :(
    15/12/2000  31/1/2000   2/12/1999   0 <-- :(
    18/12/2000  31/1/2000   2/12/1999   0 <-- :(
    19/12/2000  31/1/2000   2/12/1999   0 <-- :(
    20/12/2000  31/1/2000   2/12/1999   0 <-- :(
    21/12/2000  31/1/2000   2/12/1999   0 <-- :(
    22/12/2000  31/1/2000   2/12/1999   0 <-- :(
    25/12/2000  31/1/2000   2/12/1999   0 <-- :(
    26/12/2000  31/1/2000   2/12/1999   0 <-- :(
    27/12/2000  31/1/2000   2/12/1999   0 <-- :(
    28/12/2000  31/1/2000   2/12/1999   0 <-- :(
    29/12/2000  31/1/2000   2/12/1999   0 <-- :(
    01/01/2001  31/1/2001   2/12/2000   1
    02/01/2001  31/1/2001   2/12/2000   1
    03/01/2001  31/1/2001   2/12/2000   1
    04/01/2001  31/1/2001   2/12/2000   1
    05/01/2001  31/1/2001   2/12/2000   1
    08/01/2001  31/1/2001   2/12/2000   1
    09/01/2001  31/1/2001   2/12/2000   1
    10/01/2001  31/1/2001   2/12/2000   1
    11/01/2001  31/1/2001   2/12/2000   1

The formula works fine as long as the periods do not cross between years. However, in the event the Upper and Lower limit cross through between 2 different years, the Valid formula will exclude the previous year's values.

Is there any solution to this?

This looks like a good example for a Segment Tree. en.wikipedia.org/wiki/Segment_tree Are you familiar with algorithms and VBA? — Vityata
yep. I can code a solution with vba. however, for the legacy sake of this sheet (which is more likely than not be passed down to someone else), i would prefer an excel formula solution to this. — adam

Ron Rosenfeld Ron Rosenfeld · Accepted Answer · 2017-06-19T11:10:31

To do this with formulas, set up two more helper columns

lower1: =DATE(YEAR(Date)-1,MONTH(StartDate),DAY(StartDate))
upper1: =C8+2*LookBack
Lower2: =DATE(YEAR(Date),MONTH(StartDate),DAY(StartDate))
Upper2: =Lower2+2*LookBack

valid:  =IF(OR(AND(Date>=lower1,Date<=upper1),AND(Date>=Lower2,Date<=Upper2)),1,0)

All we are doing is setting up two sets of bounds. Relative to Date, one starts with the previous year; the other starts with the current year. Then check Date to see if it falls within either set.

For appearances, there is no requirement that the bounds columns be visible, or even that they be contiguous with the Date and valid columns.

Identifying dates that falls in the same month, day period across years

1 Answers