If/AND formula - calendar month from today??

weaholt

New Member
Joined
Oct 9, 2006
Messages
16
Hope someone can help – I have been struggling with this for ages!

I have a formula with a multiple IF/AND statement incorporating dates and I just can’t get it to work.

The conditions are as follows – A1 must be greater than 0, A2 must have YES entered, A3 contains a date which must be within 1 calendar month. If these conditions are satisfied then the value from cell B1 is displayed, if they are not satisfied then the cell remains blank.

I have – =IF(AND($A1>0,A2="YES",A3>=TODAY()+28),B1,"")

The plus 28 days from today part doesn’t work because I want one calendar month. I tried calculating the start and the end of the month in 2 helper cells and then including something in the formula to day that if TODAY() is between these dates, but I get an error? It’s driving me mad!!

TIA 
 
Last edited:

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Kamolga

Well-known Member
Joined
Jan 28, 2015
Messages
1,176
I would replace

Code:
[LEFT][COLOR=#333333][FONT=Verdana]A3>=TODAY()+28
by

Code:
month(A3)=month(today()), year(A3)=year(today())
[/FONT][/COLOR][/LEFT]
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,169
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
can use

A3>=edate(today(),1)
 

weaholt

New Member
Joined
Oct 9, 2006
Messages
16
So sorry - my original post should have said that A3 must be more than one calendar month from today, not within!

Thank you for responding :)
 

Kamolga

Well-known Member
Joined
Jan 28, 2015
Messages
1,176
So sorry - my original post should have said that A3 must be more than one calendar month from today, not within!

Thank you for responding :)
Today is 13 March 2019.

A3 must be bigger or equal to 1 March 2019:
Code:
A3>=date(year(today()),month(today()),1)

A3 must be bigger or equal to 1st April 2019

Code:
A3>=date(year(today()),month(today())+1,1)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,971
Messages
5,525,967
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top