search formula

[Marcin4111]

Hi,
I try to figure out how to custom data validation for cell to define LEN=10 and SEARCH for double "-".
So far I have customed search for LEN=10 and single "-", and it looks like this:

=AND(LEN(E34)=10;SEARCH("-";E34))

So currently for date it looks like 2014-10 31
I wish to make it like 2014-10-31
Is it also possible to define "-" as 5th and 8th mark from left?

Regards,
Marcin

[Richard Buttrey]

Him

If you're trying to ensure a date is entered to a cell why not use the DATE parameter in the validation definition?

You seem to be over complicating this and the end result will still only be some text that happens to look like a date but which will be worthless for any date arithmetic calculations.

[Marcin4111]

I cannot use DATE parameter, because after setting the date I export it to different file... The only format that can be used in different file is yyyy-mm-dd, while DATE parameter allows for yyyymmdd or yyyy/mm/dd. The only possible format that might be typed by user should be yyyy-mm-dd. Can you help me to handle it?

[Richard Buttrey]

Hi,

I still think you'rec approaching this the wrong way.

First get the right date using the Date Validation parameter. So with the Date validated in say A1
in another cell say B1 have the following function

=TEXT(A1,"yyyy-mm-dd")

Then when you export the file use the B1 value

[Marcin4111]

Actually,
It walks around but works well.
Thanks for idea.

Regards,
M.