IF Statemement using dates.

[Nath]

Hi,

I wish to use a date in my IF statement. I have tried all
sorts of combinations in order to get it to work, but i
dont seem to know the correct syntax for this. I have
tried various methods, i think that it is my parenthesis,
if so how do i do this, i want something along the lines
of

=if(c3>23/03/2003,"Yes","No")

TIA

Nathan.

 

[Norman Harker]

Hi Nathan!

If you are going to hard code a string date:

=IF(C3>--"2003-03-23","Yes","No")

Or [not suitable for non-English language settings]

=IF(C3>--"23-Mar-2003","Yes","No")

Or [a bit more structured and more flexible in terms of variation by
cell referencing the three parts of a date]

=IF(C3>DATE(2003,3,23),"Yes","No")

Or [following your attempt but stuffs up with US and other foreign
languages <vbg> ]

=IF(C3>--"23/3/2003","Yes","No")

What's with the -- ?

The string quotes are telling Excel you have a string. The -- forces
Excel to try and evaluate the string as a number and if the string is
in a form that Excel recognises as a date, then it will return a date.

 

[Frank Kabel]

Hi
some ways:
=IF(C3>DATE(2003,3,23),"Yes","No")
or
=IF(C3>--"2003-03-23","Yes","No")

 

[Aladin Akyurek]

Norman,

How about...

=IF(C3>(--"2003-03-23"),"Yes","No")

or just

=IF(C3>"2003-03-23"+0,"Yes","No")

for readability?

Aladin

 

[Norman Harker]

Hi Aladin!

I'm inclined to agree. especially the + 0. However, I think that most
users will need an explanation of what Excel is doing whichever
approach we adopt of coercing the string to numeric.

More important to me is the use of the yyyy-mm-dd string form as AFAIK
this is interpretable by any version of Excel.

 

[hgrove]

Nath wrote...

I wish to use a date in my IF statement. I have tried all sorts of
combinations in order to get it to work, but i dont seem to know
the correct syntax for this. I have tried various methods, i think
that it is my parenthesis, if so how do i do this, i want
something along the lines of

=if(c3>23/03/2003,"Yes","No")

Alternatives:

=IF(C3>DATE(2003,3,23),"Yes","No")

=IF(C3>DATEVALUE("23/03/2003"),"Yes","No")

=IF(C3>--"23/03/2003","Yes","No"