If Statement involving a Date

Moxioron

Active Member
Joined
Mar 17, 2008
Messages
420
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)))
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

redmosquito98

New Member
Joined
Mar 31, 2008
Messages
25
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)))
 

excelpunk

Board Regular
Joined
May 6, 2011
Messages
165
Did you try =if(datevalue(min(A2:A50000))>Datevalue(AB2),NA(), else whatever is your requiremnet)
 

Moxioron

Active Member
Joined
Mar 17, 2008
Messages
420
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.
 

excelpunk

Board Regular
Joined
May 6, 2011
Messages
165

ADVERTISEMENT

I think you dont require datevalue. Datevalue is used in vba. So the formula can be =if(min(A2:A50000)>AB2,"NA()", else condition)
 

Moxioron

Active Member
Joined
Mar 17, 2008
Messages
420
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)))
 

Moxioron

Active Member
Joined
Mar 17, 2008
Messages
420

ADVERTISEMENT

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.
 

excelpunk

Board Regular
Joined
May 6, 2011
Messages
165
So are you saying na() is not displayed? If so, did you try putting them in quotes "na()"
 

Moxioron

Active Member
Joined
Mar 17, 2008
Messages
420
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.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,795
Messages
5,598,093
Members
414,212
Latest member
suxrule

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
Top