Challenging If/And statement help please

Munnah

New Member
Joined
Sep 23, 2019
Messages
1
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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")
 
Upvote 0
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")
 
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
1,212,929
Messages
6,110,743
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top