"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.
|