Check if date is between 2 dates sequentially

0
votes

I have a dynamic list of dates, lets say for instance they're 3 months apart, however these could change to 5 months, 2 months etc apart

I then have a list of dates 1 month apart. What I need to do is check which date from the 1 month apart list fall between which months in the dynamic list.

For instance:

Dynamic List

01-Jun-12
01-Sep-12
01-Dec-12
01-Mar-13
01-Jun-13

Static List

01-Jun-12
01-Jul-12
01-Aug-12
01-Sep-12
01-Oct-12
01-Nov-12
01-Dec-12
01-Jan-13
01-Feb-13
01-Mar-13
01-Apr-13
01-May-13
01-Jun-13

To put it simply, it would check if 01-Jun-12 appears between 01-Jun-12 and 01-Sep-12. If it does, return the earliest month.
Then it would check where each line of the static list falls between each 2 months in the dynamic list and then return the month that comes before it.

ie. Jul falls between 01-Jun-12 and 01-Sep-12 so would return 01-Jun-12. 01-Oct-12 falls between 01-Sep-12 and 01-Dec-12 so would return 01-Sep-12

1
excelvba

1 Answers

2
votes

A simple LOOKUP formula should do this:

Assume dynamic list in A2:A6 and static list in C2:C20 then put this formula in D2 copied down

=LOOKUP(C2,A$2:A$6)

That will give you an error if C2 < A2 - is that possible?

Assumes A2:A6 are in ascending order as per your example