+ Reply to Thread
Results 1 to 45 of 45

Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

  1. #1
    Forum Contributor
    Join Date
    12-11-2017
    Location
    Colombo
    MS-Off Ver
    2013
    Posts
    109

    Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Hi,
    this is related with following solved post.

    https://www.excelforum.com/excel-pro...ml#post4802808

    In here,I need following validation also.
    When copy and paste data from another worksheet or workbook under the "Date" field column, I need to automatically clear all the invalid dates.

    Please Login or Register  to view this content.
    When I trying to add invalid date into the cell, This code is working properly.
    When copying and pasting data from another sheet or workbook, this is allowed to paste invalid dates. Therefore, I need to automatically clear all invalid dates while copy and pasting data.

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    @inoka
    Forum member jindon was very patient and helped you a lot on the other thread

    You are making the code very complicated because you are not controlling what is input into your spreadsheet - which is bad practice
    Every time you find a new problem perhaps the code needs adjusting again
    The user is entering data that the code is then rejecting which is a waste of effort
    I will help you in a different way
    Instead of making the code more complicated perhaps it would be better to help you make the input better

    Why is the input bad?
    How is the user inputting dates?
    Are you using data validation?
    Is the user copying and pasting bad dates from somewhere else?

    Making the input good
    Try the attached workbook
    Try to enter any date in column B that is before today - it will not let you
    - but you can enter tomorrow's date etc
    - look at data validation on the cell to see how that works
    - use a different validation to suit your needs
    - perhaps the user could select date from a dropdown etc
    Try to copy and paste something into column B - it will not let you
    - VBA protects the data validation
    - see code below to see how that works

    Place code in sheet module
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by kev_; 04-06-2018 at 05:01 AM.
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    17,889

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    12-11-2017
    Location
    Colombo
    MS-Off Ver
    2013
    Posts
    109

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Hi jindon,
    thank you so much for your response. your previous code was more helpful for me. It's great.
    This time, I need to enhance that code to following .
    * When copying and pasting data from another sheet or workbook to this date columns, I need to automatically clear all invalid data which has invalid date format.

    Ex: copy and paste bulk of data to the K column. (this column is date field and it has validated as DD/MM/YYYY format by using your code - (refer below code))
    among these bulk of copied data, there can be invalid date format data. (ex: 14-09-2019, 09.2.2019, 4/26/2018 etc)
    Then, after copy and paste, I need to automatically clear invalid data which are not matched with following code validation)

    Please Login or Register  to view this content.
    Hope you get my problem clearly.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,843

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    You have not responded to post #2 yet.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  6. #6
    Forum Contributor
    Join Date
    12-11-2017
    Location
    Colombo
    MS-Off Ver
    2013
    Posts
    109

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Hi AliGW ,
    I cannot understand it clearly. So, I posted my problem clearly with post #5.

  7. #7
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    What is not clear?
    You have BAD data
    Why is your data BAD?
    Can you make the data GOOD?
    Last edited by kev_; 04-07-2018 at 03:57 PM.

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    17,889

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Upload a workbook with data now and the data to be pasted, so I can test it.

    Logically my code should check all the data within intersect of columns in question and pasted range.

  9. #9
    Forum Contributor
    Join Date
    12-11-2017
    Location
    Colombo
    MS-Off Ver
    2013
    Posts
    109

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Hi jindon,
    Thank you so much for your response. As you requested, I have attached excel workbook. It has included all data and validations.
    Attached Files Attached Files

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    17,889

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    OOps...
    Please Login or Register  to view this content.
    should be
    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    12-11-2017
    Location
    Colombo
    MS-Off Ver
    2013
    Posts
    109

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Hi jindon,
    wooowww, it's working. Thank you soooo much.

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    17,889

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    No problem.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  13. #13
    Forum Contributor
    Join Date
    12-11-2017
    Location
    Colombo
    MS-Off Ver
    2013
    Posts
    109

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Hi jindon,
    Could you please help me to find solution to the following.
    Same as the date validation, I need to automatically clear invalid data over data validation cells. My attached sheet (Post #9) is included data validation column. (Column "L").
    When copy and pasting data from another source, I need to automatically remove invalid data which is not specified with data validation.

    It's great, if I can find the solution for this problem too..

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    17,889

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Do you want it like this?
    Please Login or Register  to view this content.

  15. #15
    Forum Contributor
    Join Date
    12-11-2017
    Location
    Colombo
    MS-Off Ver
    2013
    Posts
    109

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Hi jindon,
    Thank you sooo much. But, there is an issue. When copy and pasting , 2nd validation automatically converted to 1st validation data. Please find the attached excel sheet. How can I solve this. When Copy and paste, automatically clear data and cell has converted as data validation cell.
    Attached Files Attached Files
    Last edited by inoka; 04-09-2018 at 02:50 AM.

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    17,889

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Did you ask same question in your new thread?

    https://www.excelforum.com/excel-pro...ion-cells.html

  17. #17
    Forum Contributor
    Join Date
    12-11-2017
    Location
    Colombo
    MS-Off Ver
    2013
    Posts
    109

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Hi jindon,
    Yes

  18. #18
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    17,889

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Then you should wait for the reply there.

    Mark this thread as Solved.

  19. #19
    Forum Contributor
    Join Date
    12-11-2017
    Location
    Colombo
    MS-Off Ver
    2013
    Posts
    109

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Hi jindon ,
    Since this request is not related with this thread, I thought you will not reply for me. Therefore, I posted new thread. If you can find solution for me, I'll close this thread as "solved".

  20. #20
    Forum Contributor
    Join Date
    12-11-2017
    Location
    Colombo
    MS-Off Ver
    2013
    Posts
    109

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Ok. I'll mark as "solved". Thank you so much.

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    17,889

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Quote Originally Posted by inoka View Post
    Hi jindon ,
    Since this request is not related with this thread, I thought you will not reply for me. Therefore, I posted new thread. If you can find solution for me, I'll close this thread as "solved".
    You are absolutely correct.

  22. #22
    Forum Contributor
    Join Date
    12-11-2017
    Location
    Colombo
    MS-Off Ver
    2013
    Posts
    109

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Hi jindon ,
    Then , how can I get the solution to my problem. new thread has already closed.

  23. #23
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    17,889

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    I think you need ask MODERATOR.

  24. #24
    Forum Contributor
    Join Date
    12-11-2017
    Location
    Colombo
    MS-Off Ver
    2013
    Posts
    109

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Quote Originally Posted by jindon View Post
    I think you need ask MODERATOR.
    Moderator??
    What is that?

  25. #25
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,843

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    The new thread was closed because Jindon pointed straight back here for a solution. If that is not correct, then why the link back here? Jindon - please explain. If necessary I will reopen the thrad, but you will then need to remove the link and asnwer it in situ. This is getting rather confusing.

  26. #26
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    17,889

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    You can see Moderators of this Forum at the bottm.

    https://www.excelforum.com/excel-pro...ng-vba-macros/

  27. #27
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,843

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Jindon - please answer my question.

  28. #28
    Forum Contributor
    Join Date
    12-11-2017
    Location
    Colombo
    MS-Off Ver
    2013
    Posts
    109

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Quote Originally Posted by jindon View Post
    You can see Moderators of this Forum at the bottm.

    https://www.excelforum.com/excel-pro...ng-vba-macros/
    Hi jindon,

    I am not able to understand what should I do. It's great, if I can get the solution for my new thread.

  29. #29
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    17,889

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Link to show the code and I didn't need to write again.

    Any problem??????

  30. #30
    Forum Contributor
    Join Date
    12-11-2017
    Location
    Colombo
    MS-Off Ver
    2013
    Posts
    109

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Link to show the code?? where it is? I was not able to find...

  31. #31
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    17,889

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    No, it is not to you...
    why the link back here? Jindon - please explain.

  32. #32
    Forum Contributor
    Join Date
    12-11-2017
    Location
    Colombo
    MS-Off Ver
    2013
    Posts
    109

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Hi jindon ,
    I am so confused. How can I find the solution to my problem? Do I need to add new thread?

  33. #33
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    17,889

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Sorry, but I'm not in the position to solve this matter.

    As I mentioned already, one way is to send a PM to a moderator.

  34. #34
    Forum Contributor
    Join Date
    12-11-2017
    Location
    Colombo
    MS-Off Ver
    2013
    Posts
    109

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Quote Originally Posted by jindon View Post
    Sorry, but I'm not in the position to solve this matter.

    As I mentioned already, one way is to send a PM to a moderator.
    Ok. Thank you.

  35. #35
    Forum Contributor
    Join Date
    12-11-2017
    Location
    Colombo
    MS-Off Ver
    2013
    Posts
    109

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Hi jindon ,

    I'll close this thread. you gave a great support for me.

  36. #36
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,843

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    OK Inoka - so here's the thing: I closed the new thread you created because it wasn't needed. Jindon posted a link to point you back here, because it was not necessary for you to open another thread on the same (or very similar) issue. You have since opened yet another thread on the same issue which has now also been closed by another moderator.

    You really MUST read this forum's rules before posting again, bescause you agreed to abide by them when you joined here. Here's a quick link to them: https://www.excelforum.com/forum-rul...rum-rules.html

    If you continue to do your own thing and completely ignore our rules, you can expect to find yourself ultimately banned from posting altogether: it really is in your best interest to comply.

  37. #37
    Forum Contributor
    Join Date
    12-11-2017
    Location
    Colombo
    MS-Off Ver
    2013
    Posts
    109

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Extremely very sorry for it. I was so confused. That's why I posted new thread. Because, existing one is also closed. However, finally I was not able to find the solution for my data validation problem.

  38. #38
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    17,889

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Quote Originally Posted by AliGW View Post
    Jindon posted a link to point you back here, because it was not necessary for you to open another thread on the same (or very similar) issue.
    I said I gave a link there only for the reference of the code I have written here, so I didn't need to write it again and continue THERE for the new question.
    I don't think OP offend the rule.

  39. #39
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,843

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Then you have completely confused the issue by failing to explain that fully in the new thread. If the code was relevant to the new thread, then you should have posted the code in the new thread, not a link.

    Inoka & Jindon - please continue in THIS thread with the similar issue.

  40. #40
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    17,889

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    What?

    Is there such rule here???

  41. #41
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,843

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    There doesn't need to be a rule, Jindon: it's common sense.

    Inoka - we are all confused because you keep opening new threads that really aren't necessary.

    This is the end of the matter. Please continue here with your issue if it still needs resolving.

  42. #42
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    17,889

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Common sense....haha

  43. #43
    Forum Contributor
    Join Date
    12-11-2017
    Location
    Colombo
    MS-Off Ver
    2013
    Posts
    109

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Hi,
    I need this to solve.
    When copy and paste data from another sheet or workbook into the data validation column, I need to automatically clear all invalid data. and it should be remained only the valid data.
    Please find the following attached excel sheet.

  44. #44
    Forum Contributor
    Join Date
    12-11-2017
    Location
    Colombo
    MS-Off Ver
    2013
    Posts
    109

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Hi jindon ,
    Is there any solution for me.

  45. #45
    Forum Contributor
    Join Date
    12-11-2017
    Location
    Colombo
    MS-Off Ver
    2013
    Posts
    109

    Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

    Hi,
    I didn't get any solution...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)
    By inoka in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 12-14-2017, 08:30 AM
  2. Check Date Format Input into Excel Column with VBA code
    By stanlelma in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 12-12-2017, 03:08 AM
  3. [SOLVED] Userform Dynamically Generated Combobox format value as date ("mm/dd/yyyy")
    By CieloSalas in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-12-2017, 03:39 PM
  4. Replies: 8
    Last Post: 06-21-2016, 08:56 AM
  5. Replies: 3
    Last Post: 08-13-2013, 06:25 AM
  6. [SOLVED] Format a column as date format "yyyy-mm-dd"
    By Seraph122 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-11-2013, 01:30 AM
  7. Replies: 1
    Last Post: 11-01-2012, 10:37 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1