If date falls between two dates using arrays

1. If date falls between two dates using arrays

I want to write a formula that tells me which contract month the cell falls in based on an array look up of start and end dates.

My source data is a reference table with three columns. One for contract month, one for start date, and one for end date.

Input: Date
Output: Contract Month

G2=9/16/2016
A:A=Contract Month 1, 2, 3, 4....12
B:B=Start Date
C:C= End Date

Example: =IF(AND(G2>=INDEX(A:A(MATCH(G2, B:B, 0), G2<=INDEX(A:A(MATCH(G2, C:C, 0)

2. Re: If date falls between two dates using arrays

I think you might be overcomplicating this, but without some sample data and expected outcomes, it's hard to know for sure. Please provide some sample data for the three columns that reflect what is in your real file and your expected outcome.

3. Re: If date falls between two dates using arrays

Contract Month Start Date End Date
Contract-Month 1 8/5/2016 9/4/2016
Contract-Month 2 9/5/2016 10/4/2016
Contract-Month 3 10/5/2016 11/4/2016
Contract-Month 4 11/5/2016 12/4/2016
Contract-Month 5 12/5/2016 1/4/2017
Contract-Month 6 1/5/2017 2/4/2017
Contract-Month 7 2/5/2017 3/4/2017
Contract-Month 8 3/5/2017 4/4/2017
Contract-Month 9 4/5/2017 5/4/2017
Contract-Month 10 5/5/2017 6/4/2017
Contract-Month 11 6/5/2017 7/4/2017
Contract-Month 12 7/5/2017 8/4/2017

Timesheet Date
9/16/2016
9/17/2016
9/18/2016

4. Re: If date falls between two dates using arrays

Try

=INDEX(\$A\$2:\$A\$13,MATCH(1,(\$B\$2:\$B\$13<=\$G2)*(\$C\$2:\$C\$13>=\$G2),0))

Enter with Ctrl+Shift+Enter

column F has your time sheet dates

5. Re: If date falls between two dates using arrays

It worked! Thank you!
I was missing the 1 as the value in the MATCH formula, as well as the *. What functions do those serve?

6. Re: If date falls between two dates using arrays

The "1" is equivalent to TRUE and the "*" denotes an AND condition so we test if data is > Start Date AND < end date.

FYI "+" is equivalent to "OR" condition.

7. Re: If date falls between two dates using arrays

Thank you so much!

8. Re: If date falls between two dates using arrays

Originally Posted by JohnTopley
Try

=INDEX(\$A\$2:\$A\$13,MATCH(1,(\$B\$2:\$B\$13<=\$G2)*(\$C\$2:\$C\$13>=\$G2),0))

Enter with Ctrl+Shift+Enter

column F has your time sheet dates
would you be kind enough to include highlighting the equivalent cell if found, would that be possible?
example in the given date:

it should highlight cell A3

9. Re: If date falls between two dates using arrays

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1