shop-talk
[Top] [All Lists]

[Shop-talk] OT- Excel help, (before I shoot my computer)!

Subject: [Shop-talk] OT- Excel help, (before I shoot my computer)!
From: tr3driver at ca.rr.com (Randall)
Date: Wed, 14 Mar 2012 20:17:16 -0700
References: <CAFnfnRUdtNj+c6zz-of_Lrz3-uQOCGE84qH2x8yLeiwiZA6czw@mail.gmail.com> <OFB4FE8BAB.201E4975-ON852579C2.000F4599-852579C2.000FB8A0@mail.megageek.com>
> I just don't understand why Excel HAS to change the data as it is
> inputted.  

No, no, the problem is that it DOESN'T change the data as it is inputted!

The real issue is that the cell format is only applied when the date is
output.  Excel uses a special internal format to store things that it knows
are "dates", and the input routine for "dates" is selected by looking at the
system date format, not the cell format.

When you do something to make your input not be a valid date field, the
"dates" input routine fails and Excel stores it as a string instead.  That
is why doing things like entering *dd/mm/yy _seems_ to work; the input is
being stored as a string rather than a date.

Which as I said before is the key to how to do what you want to do.  Enter
the date as a string, and then write the function to parse it into year,
month and day.  Then you can use =DATE() to turn it into a "date" and
display that however you like.

-- Randall

<Prev in Thread] Current Thread [Next in Thread>