Negative date problems, and working with a leading 0 in cell

[Kalabalana]

Found out my problem to whom it may concern, I was entering in my date
in the format of mmddyyyy, so naturally excel interpreted these number
as dates I hadn't intended.

As an ammendment to my previous question, could anyone explain how t
enter dates in the mmddyyyy format and tell excel that cells in som
selection are of this format, and to interpret them as dates

 

[Dave Peterson]

You've got a few choices:

#1. Use an event macro that changes those values to dates.
Visit Chip Pearson's site to see how he does it.
http://www.cpearson.com/excel/DateTimeEntry.htm

#2. Use a helper column of cells with a formula like:
=date(right(a1,4),left(a1,2),mid(a1,3,2))
(Format as dates)
Copy|paste special|values and get rid of the original column

#3. Select your column and do data|text to columns
You'll be able to specify this field as a date (mdy).
And just plop them right back where you got them.

 

[JE McGimpsey]

See

http://cpearson.com/excel/DateTimeEntry.htm

 

[Kalabalana]

Excellent ideas, the helper column is a little stressed though, and th
data -> text is not an option as I need to perform calculations on th
dates.

So I've adopted the vb code which works great but for some odd reaso
the code does not like using dates of april 2004 and later. This i
very frustrating, if anyone with a significant foundation in this are
could help me out with this new problem, I would be very grateful

 

[JE McGimpsey]

It would be most helpful if you'd start by explaining what

"the code does not like"

means. If you significantly changed the macro, you should also post your
code.