IF(ISBLANK) with two conditions

IML_56

New Member
Joined
Jan 16, 2020
Messages
11
Office Version
  1. 2019
Platform
  1. Windows
I have two columns with a bunch of dates But only the first column also has blanks in them. I cant seem to get the formula right that ignores the banks and to have it return the correct value.

First condition: is that the first i am looking for a date that to be before 12-1-2020
Second Condition: Date in the second column must be after 1-1-2021


Current Formula that i have been working with:
=IF(ISBLANK([@[Column 1]]),IF(AND([@Column 2]>=DATE(2021,1,1), [@[Column 1]] <= DATE(2020,12,1)),"Yes","No"))

Column 1Column 2
6/1/20205/20/2022
4/25/2020
4/1/2020
12/20/20196/8/2022
3/5/20219/7/2020
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Hi & welcome to MrExcel.
What should happen if column 1 is blank?
 
Upvote 0
This?

Book1
ABC
1Column 1Column 2Column 3
26/1/20205/20/2022Yes
34/25/2020No
44/1/2020No
512/20/20196/8/2022Yes
63/5/20219/7/2020No
Sheet1
Cell Formulas
RangeFormula
C2:C6C2=IF(AND(NOT(ISBLANK([@[Column 1]])),[@[Column 2]]>=DATE(2021,1,1), [@[Column 1]] <= DATE(2020,12,1)),"Yes","No")
 
Upvote 0
Hi & welcome to MrExcel.
What should happen if column 1 is blank?
I would like for it to return the value of no since it doesnt meet the conditions.

So the third column is where i want the formula to be in where it says either yes or no if both columns meet or do not meet both of the criteria s.
 
Upvote 0
In that case how about
=IF(ISBLANK([@[Column 1]]),"No",IF(AND([@Column 2]>=DATE(2021,1,1), [@[Column 1]] <= DATE(2020,12,1)),"Yes","No"))
 
Upvote 0
This?

Book1
ABC
1Column 1Column 2Column 3
26/1/20205/20/2022Yes
34/25/2020No
44/1/2020No
512/20/20196/8/2022Yes
63/5/20219/7/2020No
Sheet1
Cell Formulas
RangeFormula
C2:C6C2=IF(AND(NOT(ISBLANK([@[Column 1]])),[@[Column 2]]>=DATE(2021,1,1), [@[Column 1]] <= DATE(2020,12,1)),"Yes","No")
FOr whatever reason the vaules are only returning "No"
 
Upvote 0
=IF(AND(NOT(ISBLANK([@[Column 1]])),[@[Column 2]]>=DATE(2021,1,1), [@[Column 1]] <= DATE(2020,12,1)),"Yes","No")

=IF(AND(NOT(ISBLANK([@[Column 1]])),AND([@[Column 2]]>=DATE(2021,1,1), [@[Column 1]] <= DATE(2020,12,1))),"Yes","No")

Either works and fits your logic...
 
Upvote 0
All three formulae work for me

Book1
ABCDE
1Column 1Column 2CSmith1FluffCSmith2
201/06/202020/05/2022YesYesYes
325/04/2020NoNoNo
401/04/2020NoNoNo
520/12/201908/06/2022YesYesYes
605/03/202107/09/2020NoNoNo
List
Cell Formulas
RangeFormula
C2:C6C2=IF(AND(NOT(ISBLANK([@[Column 1]])),[@[Column 2]]>=DATE(2021,1,1), [@[Column 1]] <= DATE(2020,12,1)),"Yes","No")
D2:D6D2=IF(ISBLANK([@[Column 1]]),"No",IF(AND([@[Column 2]]>=DATE(2021,1,1), [@[Column 1]] <= DATE(2020,12,1)),"Yes","No"))
E2:E6E2=IF(AND(NOT(ISBLANK([@[Column 1]])),AND([@[Column 2]]>=DATE(2021,1,1), [@[Column 1]] <= DATE(2020,12,1))),"Yes","No")


Are you sure your dates are real dates & not text
 
Upvote 0
All three formulae work for me

Book1
ABCDE
1Column 1Column 2CSmith1FluffCSmith2
201/06/202020/05/2022YesYesYes
325/04/2020NoNoNo
401/04/2020NoNoNo
520/12/201908/06/2022YesYesYes
605/03/202107/09/2020NoNoNo
List
Cell Formulas
RangeFormula
C2:C6C2=IF(AND(NOT(ISBLANK([@[Column 1]])),[@[Column 2]]>=DATE(2021,1,1), [@[Column 1]] <= DATE(2020,12,1)),"Yes","No")
D2:D6D2=IF(ISBLANK([@[Column 1]]),"No",IF(AND([@[Column 2]]>=DATE(2021,1,1), [@[Column 1]] <= DATE(2020,12,1)),"Yes","No"))
E2:E6E2=IF(AND(NOT(ISBLANK([@[Column 1]])),AND([@[Column 2]]>=DATE(2021,1,1), [@[Column 1]] <= DATE(2020,12,1))),"Yes","No")


Are you sure your dates are real dates & not text

Thank you, it worked. My dates were values instead being dates. So after i changed the raw data it ended up fixing it. Thank you for the help.
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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