IF(ISBLANK) with two conditions

IML_56

New Member
Joined
Jan 16, 2020
Messages
4
Office Version
2019
Platform
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
31,994
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
What should happen if column 1 is blank?
 

CSmith

Board Regular
Joined
Jan 13, 2020
Messages
105
Office Version
365, 2010, 2007
Platform
Windows, Mobile, Web
This?

xl2bb.xlam
ABC
1Column 1Column 2Column 3
26/1/20205/20/2022
34/25/2020
44/1/2020
512/20/20196/8/2022
63/5/20219/7/2020
Sheet1
Cell Formulas
Range(s)Formula
C2:C6C2=IF(AND(NOT(ISBLANK([@[Column 1]])),[@[Column 2]]>=DATE(2021,1,1), [@[Column 1]] <= DATE(2020,12,1)),"Yes","No")
 

IML_56

New Member
Joined
Jan 16, 2020
Messages
4
Office Version
2019
Platform
Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
31,994
Office Version
365
Platform
Windows
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"))
 

IML_56

New Member
Joined
Jan 16, 2020
Messages
4
Office Version
2019
Platform
Windows
This?

xl2bb.xlam
ABC
1Column 1Column 2Column 3
26/1/20205/20/2022
34/25/2020
44/1/2020
512/20/20196/8/2022
63/5/20219/7/2020
Sheet1
Cell Formulas
Range(s)Formula
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"
 

CSmith

Board Regular
Joined
Jan 13, 2020
Messages
105
Office Version
365, 2010, 2007
Platform
Windows, Mobile, Web
=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...
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
31,994
Office Version
365
Platform
Windows
All three formulae work for me

xl2bb.xlam
ABCDE
1Column 1Column 2CSmith1FluffCSmith2
201/06/202020/05/2022
325/04/2020
401/04/2020
520/12/201908/06/2022
605/03/202107/09/2020
List
Cell Formulas
Range(s)Formula
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
 

IML_56

New Member
Joined
Jan 16, 2020
Messages
4
Office Version
2019
Platform
Windows
All three formulae work for me

xl2bb.xlam
ABCDE
1Column 1Column 2CSmith1FluffCSmith2
201/06/202020/05/2022
325/04/2020
401/04/2020
520/12/201908/06/2022
605/03/202107/09/2020
List
Cell Formulas
Range(s)Formula
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
31,994
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Forum statistics

Threads
1,081,567
Messages
5,359,642
Members
400,543
Latest member
nector

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top