Consulting

Results 1 to 13 of 13

Thread: Data validation and manipulating input

  1. #1
    VBAX Regular
    Joined
    Apr 2017
    Posts
    63
    Location

    Data validation and manipulating input

    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.
    Last edited by MSXL; 09-04-2022 at 02:11 AM.

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    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.

  3. #3
    VBAX Regular
    Joined
    Apr 2017
    Posts
    63
    Location
    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).

  4. #4
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    If you know better then why ??

  5. #5
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,998
    Location
    Quote Originally Posted by MSXL View Post
    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
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  6. #6
    VBAX Regular
    Joined
    Apr 2017
    Posts
    63
    Location
    QUOTE=Aussiebear;416609]That bit clearly refers to entering dates[/QUOTE]

    Yes it does.

  7. #7
    VBAX Regular
    Joined
    Apr 2017
    Posts
    63
    Location
    Quote Originally Posted by snb View Post
    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).

  8. #8
    VBAX Regular
    Joined
    Apr 2017
    Posts
    63
    Location
    These parts are not about date formatting:
    Quote Originally Posted by MSXL View Post
    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?

  9. #9
    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)

  10. #10
    VBAX Regular
    Joined
    Apr 2017
    Posts
    63
    Location
    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.

  11. #11
    Quote Originally Posted by MSXL View Post
    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.

  12. #12
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Preventing errors/mistakes/faults is the most user friendly way of programming.

  13. #13
    VBAX Regular
    Joined
    Apr 2017
    Posts
    63
    Location
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •