Challenging If/And statement help please

[Munnah]

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

 

[ChrisGT7]

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")

 

[Joe4]

Welcome to the Board!

If your dates are in cells A1, A2, and A3, try this formula:

=IF(OR(AND(A1<=A3,A2=""),AND(A1<=A3,A2>=A3)),"Yes","No")

 

[DanteAmor]

Welcome to the forum!

Try this


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:82.69px;" /><col style="width:76.04px;" /><col style="width:102.65px;" /><col style="width:94.1px;" /><col style="width:30.42px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; ">Contract</td><td style="background-color:#ffff00; ">Customer</td><td style="background-color:#ffff00; ">Start Date</td><td style="background-color:#ffff00; ">End Date</td><td style="background-color:#ffff00; "> </td><td style="background-color:#ffff00; ">Customer</td><td style="background-color:#ffff00; ">Active</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">1</td><td >damor</td><td style="text-align:right; ">01/01/2014</td><td > </td><td > </td><td >damor</td><td >Yes</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">2</td><td >Mun</td><td style="text-align:right; ">03/01/2016</td><td style="text-align:right; ">30/08/2019</td><td > </td><td >Mun</td><td >No</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >G2</td><td >=IF(INDEX(D2:D5,MATCH(F2,B2:B5,0))="","Yes",IF(INDEX(D2:D5,MATCH(F2,B2:B5,0))>=EOMONTH(TODAY(),0),"Yes","No"))</td></tr></table></td></tr></table>