ExcelBanter - View Single Post

Ron Rosenfeld

On 26 Sep 2006 10:49:41 -0700, wrote:

Once again, I messed up with my question/request. I am really sorry
that I am having such a dificult time being clear about things. The
user will fill in the dates any way they want. If the cell contains
only a date, then I can set the cell format to yyyy-mm-dd and then
whatever the user types in, Excel will re-format it and then when I
extract the date I will get the yyyy-mm-dd format. But, when other
text is in the cell with the date, the formatting doesn't work so then
I have to deal with a variety of date formats. Also, then I have to
re-format the dates through vba after validating the cell contents.
Maybe, I could first look for something that looks sort of like a date
and then pop up a message telling the user to format it like yyyy-mm-dd
if it does not match that format, then do the validation.

That changes the algorithm.

What we will do is first look at the first word in the string.

If the "word" is recognizable as a date by VBA; or if the word starts with an
"F", then we have a chance of the entry being valid and can do the rest of the
testing.

In addition, if the first word is recognizable as a date, we will reformat it
according to your specifications.

For now, and common to using Excel, we will require that the date includes
separators (i.e. 20061225 would not be valid). If this is an issue we can
address it subsequently.

Also, in your specifications, you also wrote:

"f1,f2,f3,f4,f5,f6 (multiples must be separated by a comma and no
space)"

Is this a requirement for data input, or not?

Finally, once you have checked the data for validity, what do you want to do
with the data?

If you are going to split it out into separate cells, then what is the purpose
of the "no space" criterion?

Do you, perhaps, want it all in one cell formatted as specified?
--ron