Data validation and manipulating input

[MSXL]

Hello there, is it possible for Excel to check the length of data input is between 5 and 11 characters and if a "/" is detected, convert it into a " " (space)?

Also, is there a way to make sure any data input always appears correctly regardless of whether the user types 040922, 04/09/22, 04.09.22? 040922 converts to 12 04 1957. I need to be able to cover all eventualities as different people enter dates differently. I also need to anticipate some not using leading zeros.

I've tried setting data validation to text between x and x length but it doesn't throw an error if it's shorter or longer, is there a more reliable formula?

Thanks.

[snb]

1. let the user select a valid year
2. let the user select a valid month
3. let the user select a valid day

Show the user only valid choices, so you won't have to check afterwards.

[MSXL]

It needs to be contained within one cell and the output needs to be converted into a date regardless to be pasted into other software.

The data validation I was referring to was in relation to other cells not incorporating dates, take planning references for example (they use a slash and if the user copied a planning reference into a cell, Excel should remove the slash and replace it with a space).

[snb]

If you know better then why ??

[Aussiebear]

Also, is there a way to make sure any data input always appears correctly regardless of whether the user types 040922, 04/09/22, 04.09.22? 040922 converts to 12 04 1957. I need to be able to cover all eventualities as different people enter dates differently. I also need to anticipate some not using leading zeros.


That bit clearly refers to entering dates

[MSXL]

QUOTE=Aussiebear;416609]That bit clearly refers to entering dates[/QUOTE]

Yes it does.

[MSXL]

If you know better then why ??

Not sure what you mean. I asked for a way to allow all users to input dates however they format it, so that is still recognised as a date by Excel. All parts of the date should be entered into a single cell and then whether it is entered with a leading zero or not, whether they use a slash, dot or spaces or not, what they enter should be recognised as a date (and the correct date too, as pointed out in my previous example).

[MSXL]

These parts are not about date formatting:


is it possible for Excel to check the length of data input is between 5 and 11 characters and if a "/" is detected, convert it into a " " (space)?

I've tried setting data validation to text between x and x length but it doesn't throw an error if it's shorter or longer, is there a more reliable formula?

[arnelgp]

maybe you can "control" the input of the user, by adding additional Label that gives instruction
of the acceptable date format system will accept.
instead of You adjusting to the Entry of the users, You implement the Rule so that instead of You adjusting
to their input, you specifically Instruct them how to fill up the field, example:


Cell(A1): Enter date (format: mm-dd-yyyy): (cell A2 is their input)

[MSXL]

I understand that's a way to do it, it's not the most user friendly or efficient in my scenario though.

I'm trying to help make life easier by allowing people to enter the date format as they wish and paste values (dates and others data) and then have them get stored in the correct format.

[arnelgp]

I understand that's a way to do it, it's not the most user friendly or efficient in my scenario though.

I'm trying to help make life easier by allowing people to enter the date format as they wish and paste values (dates and others data) and then have them get stored in the correct format.
well that will require you Lots of code and transformation.
in reality, you need to be in Control (not user friendly but, i think is more systematic).

one scenario, if you are a security guard and people only allowed to enter on
a "specific gate" at "specific time". since that is not user-friendly, you allow them to
take control and people goes in-out on all gates every now and then.
what you have is chaos.

even in business, there is so called business rules.
you obey the rule and you are in business.

you may excuse that this is only excel. but having "bad" data will
bring you "bad" reporting, etc.

[snb]

Preventing errors/mistakes/faults is the most user friendly way of programming.

[MSXL]

Could we please at least look at a way of Excel via VBA checking and converting data in I16 for a "/" and changing it to a space? Sometimes it may be typed in with a space and other times it could be pasted in and will contain a slash.