putting a date in an if statement
in [Functions]
|
Prev: How to retain part of formula's criteria as static
Next: How to have a cell auto resize the font to make text fit in cell?
From: pat67 on 21 May 2010 13:48 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?
From: T. Valko on 21 May 2010 14:04 >=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) -- Biff Microsoft Excel MVP "pat67" <pbuscio(a)comcast.net> wrote in message news:0002e7c8-77dd-4995-b59e-ae533583e7f7(a)o39g2000vbd.googlegroups.com... > 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?
From: pat67 on 21 May 2010 15:28 On May 21, 2:04 pm, "T. Valko" <biffinp...(a)comcast.net> wrote: > >=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) > > -- > Biff > Microsoft Excel MVP > > "pat67" <pbus...(a)comcast.net> wrote in message > > news:0002e7c8-77dd-4995-b59e-ae533583e7f7(a)o39g2000vbd.googlegroups.com... > > > > > 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?- Hide quoted text - > > - Show quoted text - thanks
From: jayray on 21 May 2010 15:33 On May 21, 2:04 pm, "T. Valko" <biffinp...(a)comcast.net> wrote: > >=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) > > -- > Biff > Microsoft Excel MVP > > "pat67" <pbus...(a)comcast.net> wrote in message > > news:0002e7c8-77dd-4995-b59e-ae533583e7f7(a)o39g2000vbd.googlegroups.com... > > > > > 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?- Hide quoted text - > > - Show quoted text - Or you can put it as =IF(J2<DATEVALUE("6/1/2010"),"N/A",J2)
From: Chip Pearson on 21 May 2010 17:26
>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 On Fri, 21 May 2010 12:33:34 -0700 (PDT), jayray <johntjia(a)gmail.com> wrote: >On May 21, 2:04�pm, "T. Valko" <biffinp...(a)comcast.net> wrote: >> >=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) >> >> -- >> Biff >> Microsoft Excel MVP >> >> "pat67" <pbus...(a)comcast.net> wrote in message >> >> news:0002e7c8-77dd-4995-b59e-ae533583e7f7(a)o39g2000vbd.googlegroups.com... >> >> >> >> > 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?- Hide quoted text - >> >> - Show quoted text - > >Or you can put it as =IF(J2<DATEVALUE("6/1/2010"),"N/A",J2) |