Function Pitfalls in Celll Formatting
 

Does anybody know if incorrect cell formatting can cause issues in functions?
That is do I need to be cognisent of incorrect formats when undertaking
calculations?

That is, I know that I can format a cell as text. If the cell in question
is a number say "100". If I format this as text, it doesn't seem to cause
any issues in funtions.

I also know that if I put the wrong number of days in month (30/02/2005) or
the wrong number of months (30/13/2005) Excel automatically refuses to turn
this into a serial number and the contents of the cell is left justified to
reflect that fact that Excel does not treat it as a valid date. And this
will obvilously cause issues in Date Functions. However its not clear to me
if its the format or simply the wrong days/months.

I also know that in the UK, if I type in "£100", the £ dissapears
immediately and the cell recieves the format #,##0_); (#,##0); "0"_).
However this does not happen if I type in "$100". The entry is treated like
text and is left justified. But this creates a #VALUE! error if included in
a function. Is this because its formatted as text or because the functions
cannot deal with the "$" character. I am also wondering this is a regional
setting (ie if I were in the US would this occur if I used the $ instead).

Thanks in Advance

"ExcelMonkey" wrote in message
...

I also know that if I put the wrong number of days in month (30/02/2005)
or
the wrong number of months (30/13/2005) Excel automatically refuses to
turn
this into a serial number and the contents of the cell is left justified
to
reflect that fact that Excel does not treat it as a valid date. And this
will obvilously cause issues in Date Functions. However its not clear to
me
if its the format or simply the wrong days/months.

Because it is not a valid date, and it includes text characters, /, then
Excel will default it to text. If you enter =DATE(2005,2,30) in a cell,
Excel knows that is a date, so calculates the 30th day of Feb, the 2nd
March.

So, IMO, nothing is wrong per se, it is just the action Excel takes
depending on the information provided.

I also know that in the UK, if I type in "£100", the £ dissapears
immediately and the cell recieves the format #,##0_); (#,##0); "0"_).
However this does not happen if I type in "$100". The entry is treated
like
text and is left justified. But this creates a #VALUE! error if included
in
a function. Is this because its formatted as text or because the
functions
cannot deal with the "$" character. I am also wondering this is a
regional
setting (ie if I were in the US would this occur if I used the $ instead).

This is caused by the regional settings on your machine. You have set it to
English (United Kingdom), so £ is the default currency. If you change this
to English (United States) you will see the opposite actions with that same
data. Again, the $100 is treated as text as Excel does not know it is
currency, and it has a text character.