putting a date in an if statement

[pat67]

When i write an if statement taht says this =IF(J2<6/1/2010,"N/A",J2),
it always comes up false. How do make it see the date?

 

[T. Valko]

=IF(J2<6/1/2010,"N/A",J2)

Excel evaluates that as: IF J2 is less than 6 divided by 1 divided by 2010.

The best way to do this is to use a cell to hold the date:

A1 = 6/1/2010

Then just refer to that cell:

=IF(J2<A1,"N/A",J2)

Or, use the DATE function:

=IF(J2<DATE(2010,6,1),"N/A",J2)

 

[pat67]

Excel evaluates that as: IF J2 is less than 6 divided by 1 divided by 2010.

The best way to do this is to use a cell to hold the date:

A1 = 6/1/2010

Then just refer to that cell:

=IF(J2<A1,"N/A",J2)

Or, use the DATE function:

=IF(J2<DATE(2010,6,1),"N/A",J2)

--
Biff
Microsoft Excel MVP






- Show quoted text -

thanks

 

[jayray]

Excel evaluates that as: IF J2 is less than 6 divided by 1 divided by 2010.

The best way to do this is to use a cell to hold the date:

A1 = 6/1/2010

Then just refer to that cell:

=IF(J2<A1,"N/A",J2)

Or, use the DATE function:

=IF(J2<DATE(2010,6,1),"N/A",J2)

--
Biff
Microsoft Excel MVP






- Show quoted text -

Or you can put it as =IF(J2<DATEVALUE("6/1/2010"),"N/A",J2)

 

[Chip Pearson]

Or you can put it as =IF(J2<DATEVALUE("6/1/2010"),"N/A",J2)

I would be careful with that due to international settings. In the US,
"6/1/2010" means 1-June-2010, but in Europe it means 6-January-2010. I
would recommend that you use the DATE function to unambiguously get a
date. E.g., DATE(2010,6,1)

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Excel evaluates that as: IF J2 is less than 6 divided by 1 divided by
2010.

The best way to do this is to use a cell to hold the date:

A1 = 6/1/2010

Then just refer to that cell:

=IF(J2<A1,"N/A",J2)

Or, use the DATE function:

=IF(J2<DATE(2010,6,1),"N/A",J2)

--
Biff
Microsoft Excel MVP






- Show quoted text -

thanks