Hi Frank!

I think the otiose code goes back. I can live with the re-entry
problem unless there's another way.

I'm not getting leading 0 problems in the original because I had text
to parse. Only 0 problem was impossible 0 days and months.

That conversion of dates back to serial numbers on re-selection was
also a real problem.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Frank Kabel" wrote in message
...
Hi Bob, Norman

I have some problems with this version <vbg
1. You're not able to enter any formulas in this range anymore as
the
Selection_Change event formats the cell to 'Text' and a formula is
no
longer recognized

2. You can't calculate with the resulting value as it's stored as
'Text.

One could help the second one if you use
With Target
'In goes the parsed date
.NumberFormat = DateFormat
.Value = DateValue(DateStr)
End With

but this will lead to a conversion of the entered dates to their
serial
number if you select them again. To prevent this Norman inserted his
'otiose code' but this will lead to problems for re-entries and
leading
zeros...

So I would prefer Norman's first solution (with Bob's additions in
respect to contant values) and live with the 'leading zeros'
problem.


--
Regards
Frank Kabel
Frankfurt, Germany

"Bob Phillips" schrieb im
Newsbeitrag ...
Norman,

You have re-introduced the problem whereby the initial input works
okay, but
re-input into a cell without moving away and back again and it goes
bang.

Here is a modification, removing the otiose code (that gave me
problems),
changing the final assignment of the value, and with a couple of
constants
to allow more friendly definition of the test range and date format
(i.e.
easier to change).