If Statement involving a Date

Moxioron

Active Member
Joined
Mar 17, 2008
Messages
436
Office Version
  1. 2019
Hello all.

I have looked high and low for an answer on this and i am sure it is right in front of me.

I want to return NA() if the date value in AB2 is less than the date values in A2:A50000. With the formula below I get a #VALUE! result.

Where am I going wrong? Thank you.


=IF(Data!$A$2:$A$50000>DATEVALUE(AB2),NA(),SUMPRODUCT((Data!$A$2:$A$50000>=AB$2)*(Data!$A$2:$A$50000<=AB$3)*(Data!$D$2:$D$50000=$A6),(Data!$E$2:$E$50000)))
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Just throw quotes around the NA() if the proper error doesn't matter. Like:
=IF(Data!$A$2:$A$50000>DATEVALUE(AB2),"NA()",SUMPRODUCT((Data!$A$2:$A$50000>=AB$2)*(Data!$A$2:$A$50000<=AB$3)*(Data!$D$2:$D$50000=$A6),(Data!$E$2:$E$50000)))
 
Upvote 0
Did you try =if(datevalue(min(A2:A50000))>Datevalue(AB2),NA(), else whatever is your requiremnet)
 
Upvote 0
Unfortunatly neither of those worked.

=IF(DATEVALUE(MIN(Data!$A$2:$A$50000))>DATEVALUE(AB$2),NA(),SUMPRODUCT((Data!$A$2:$A$50000>=AB$2)*(Data!$A$2:$A$50000<=AB$3)*(Data!$D$2:$D$50000=$A6),(Data!$E$2:$E$50000)))

=IF(Data!$A$2:$A$50000))>DATEVALUE(AB$2),"NA()",SUMPRODUCT((Data!$A$2:$A$50000>=AB$2)*(Data!$A$2:$A$50000<=AB$3)*(Data!$D$2:$D$50000=$A6),(Data!$E$2:$E$50000)))

Both return a #Value! as well.
 
Upvote 0
I think you dont require datevalue. Datevalue is used in vba. So the formula can be =if(min(A2:A50000)>AB2,"NA()", else condition)
 
Upvote 0
Although I don't get the #Value! anymore, it appears to ignore the If statement.

I don't have any records in Data greater than the value in AB2, which in this case is 08/01/2012.

=IF(MIN(Data!$A$2:$A$50000)>AB$2,NA(),SUMPRODUCT((Data!$A$2:$A$50000>=AB$2)*(Data!$A$2:$A$50000<=AB$3)*(Data!$D$2:$D$50000=$A6),(Data!$E$2:$E$50000)))
 
Upvote 0
Just a note...I want the NA() because the data is also being graphed and if there is no data greater than the value in AB2, then I want the graph to ignore it. Thanks.
 
Upvote 0
I want the cell to reflect N/A# if the date reflected in AB2 is greater than the range A2:A50000. So I don't want quotes around the NA(). However, I tried it with quotes and for some reason with or without quotes the If statement is ignored. THanks.
 
Upvote 0
I think you need it to be the other way round, If no date in that range is greater than AB2 then the MAX value will be <= AB2 so try

=IF(MAX(Data!$A$2:$A$50000)<=AB2,NA(),SUMPRODUCT((Data!$A$2:$A$50000>=AB$2)*(Data!$A$2:$A$50000<=AB$3)*(Data!$D$2:$D$50000=$A6),Data!$E$2:$E$50000))
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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