1. ## Challenging If/And statement help please

Good day!

I hope someone can assist me with the following problem

I am trying to get a return value of Yes/No indicating if the customer is active for the current month based on the following (which are all dates)

Example 1
Contract Start Date: 01/01/2014
Contract End Date: *blank*
Current Month: 09/01/2019

In this case, the customer the customer is active for 9/2019.

Example 2
Contract Start Date: 03/01/2016
Contract End Date: 08/31/2019
Current Month: 09/01/2019

In this case, the customer the customer is NOT active for 9/2019.

Can someone please assist. I am really frustrated. Thanks

2. ## Re: Challenging If/And statement help please

Hi Munnah,

I assume that B1 = Start Date, B2 = End Date, B3 = Current Month.

In any other cell, type the following formula:
=IF(AND(B3>=IF(NOT(ISNUMBER(B1)),DATEVALUE("1/1/1900"),B1),B3<=IF(NOT(ISNUMBER(B2)),DATEVALUE("12/31/9999"),B2)),"Active","Inactive")

3. ## Re: Challenging If/And statement help please

Welcome to the Board!

If your dates are in cells A1, A2, and A3, try this formula:
Code:
=IF(OR(AND(A1<=A3,A2=""),AND(A1<=A3,A2>=A3)),"Yes","No")

4. ## Re: Challenging If/And statement help please

Welcome to the forum!

Try this

 A B C D E F G 1 Contract Customer Start Date End Date Customer Active 2 1 damor 01/01/2014 damor Yes 3 2 Mun 03/01/2016 30/08/2019 Mun No

 Cell Formula G2 =IF(INDEX(D2:D5,MATCH(F2,B2:B5,0))="","Yes",IF(INDEX(D2:D5,MATCH(F2,B2:B5,0))>=EOMONTH(TODAY(),0),"Yes","No"))