Simple IF problem

[Trevor Aiston]

I have a spreadsheet which contains a comumn (C) headed REFERALRECEIVED,
which contains a list of dates un UK dd/mm/yyyy format.

I want to indicated in column N the financial year that data represnts (data
only covers two calander years) so have =IF(C2<="31/03/2009","0809","0910")
what I get is 0809 regardless of date in C column (e.g 24/08/2009). If I
remove the "" from C2<=31/03/2009 I get 2009 regardless of date?

What am i doing wrong

 

[Trevor Aiston]

Thanx barry worked a treat, can't see that documented anywhere. Am I missing
someting in the help files?

Trev

 

[Rick Rothstein]

I'm not sure it is documented anywhere, but what is going on is that Excel goes out of its way to try and help you out (sometimes, it goes to far and gets in the way, such as with an entry like 1/60 where it sees that as a Date)... to that end, if you involve a text string in a calculation and if Excel can make sense of that text string as a number, then it will convert the text string to the number it sees it as and then perform the calculation. Adding 0 to a number does not change its value and it is one of the methods used to involve a text string in a calculation so that Excel will convert the text string to a numeric value (Dates, to Excel, are numeric values) without changing the value represented by the text string. Other ways to force the calculation is to multiply by 1 or affix two minus signs in front of the text string (two minus signs is the same as multiplying by minus one twice... minus one time minus one equals plus 1). Oh, going back to the "Dates, to Excel, are numeric values"... if you are not aware, Excel stores Dates as floating point numbers... the integer portion is the number of days since January 1, 1900 and the decimal portions is the fraction of a 24-hour day that has passed (for example, 6:00am would be 6/24 which is 0.25).

 

[JoeU2004]

Thanx barry worked a treat, can't see that documented anywhere.
Am I missing someting in the help files?

You won't find the specific construct documented in Excel help pages because
this has to do with programming methodology and algorithms, not specific
usage.

What you will find, if you stumble around enough, is that the use of a
string like "31/3/2009" for a date is deprecated. The preferred form is
DATE(2009,3,31). The reason is: the interpretation of a string like that
depends on the Regional and Language options on the computer where the file
is read. Consider ambiguous dates like "1/2/2009".

As for Barry's use of "31/3/2009"+0....

First, many people would write --"31/3/2009" (double negative). But the
choice is arbitrary.

The key point is: when you combine a date string in any arithmetic
operation, it is converted to a number, a "date serial number", which is how
Excel stores dates and time internally.

Your original formulation, C2<="31/3/2009", did not work as you expected
because you have an expression of the form number<=text, since C2 is
presumable a "date serial number".

In that form, Excel does not convert the date string to a number (!).
Instead, number<=text always results in TRUE (!).

I do not believe that is documented anywhere. There are many lapses ( and
misstatements :-( ) in Microsoft documentation.


----- original message -----

 

[Fred Smith]

If you saw the following equation:
=1/3*4
you would recognize it as arithmetic operations. To Excel:
=3/31/09
is the same thing: 3 divided by 31 divided by 9.

If you saw the following:
"Text"
you would recognize it as text. To Excel:
"3/31/09"
is the same thing: it's text.

Recent versions of Excel will allow you to perform arithmetic on text. When
you do, Excel tries to interpret what's in the text. That's why:
=--"3/31/09" and
="3/31/09"+0
works. The arithmetic operator forces Excel to interpret the text as a
number, which it recognizes as a date.

Since version 1, Excel has a date function, as did Lotus, which avoids all
of the above potential mis-interpretations. When you use:
=date(2009,3,31)
everyone, including Excel, knows what you mean.

Regards,
Fred

 

[Trevor Aiston]

Thanks to all you have answered my quaestion and given such clear
explanations what I was doing wrong.

I understand (the basics!) about calculations on text etc, but had assumed
that by formating the cells as a date,e excel would recognise the valu I was
calculating as a date. So am I now right to understand that formating a cell
does not actually change the underlying data, just how its presented ....
hence my problem?

Trev

 

[JoeU2004]

So am I now right to understand that formating a cell does not actually
change the underlying data, just how its presented ....

Well, yes.

hence my problem?

Well, no.

First, using a date (or any other non-Text) format only affects the
appearance of __numeric__ values in a cell; and yes, it affects only their
appearance, not their underlying value.

But a numeric format has no effect on text in the cell.

Nevertheless, that has nothing to do with why your original formula failed.

Your original formula included the comparison C2<="31/03/2009".

C2 is the cell that is presumably formatted as Date. And Excel correctly
interpreted it as such.

The issue is not with a cell's format, but with the interpretation of the
text "31/03/2009".

Excel does not interpret text as a number in a conditional comparison (!).
But it does interpret text as a number in an arithmetic expression, when
feasible.

So "31/03/2009"+0 or --"31/03/2009" causes Excel to interpret the text as a
date and replace it with a date serial number in the comparison. That
allows for a proper comparison with the date serial number in C2,
persumably.

However, DATE(2009,3,31) is better because Excel will consider "31/03/2009"
as mm/dd/yyyy or dd/mm/yyyy depending on the Regional and Language options
on the computer the opens the Excel file. It might work fine on your
computer, which presumably is set to dd/mm/yyyy; but it does not work on my
computer, for example, which is set to mm/dd/yyyy.


----- original message -----

 

[Trevor Aiston]

Thanks, your point abot it not being the formating but the presnation of
31/03/2009 as being the problem occured to me shortly after posting.

But thanks for helping me clarify
Trevor