+ Reply to Thread
Results 1 to 25 of 25

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,

    I would like to check the format of dates entered or pasted into a Excel Spreadsheet column with VBA code.
    The date format must be DD/MM/YYYY. A message box should appear if any invalid data is found and the option to clear the individually cell with the bad entry or cancel the entire input provided.

    This issue same as the following question. But, onlty difference is, in here date format should be "DD/MM/YYYY".

    https://www.excelforum.com/excel-pro...-vba-code.html

    Could you please provide the VBA code for me to test?

  2. #2
    Valued Forum Contributor ranman256's Avatar
    Join Date
    07-29-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2003
    Posts
    1,171

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

    Try this:

    usage: ValidateDate ActiveCell.Text


    Please Login or Register  to view this content.

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

    Thank you. Could you please send me the excel file.

  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)

    It is shown a Run-time error '13'. Could you please send me excel file.

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

    If I add date as "2019-09-12", it is shown a debug error. Could you please help me to find this error.
    Also, if I add "10/25/2017", it is not validated. I need to validate while entering any incorrect format. Thanks.
    Last edited by inoka; 12-12-2017 at 01:38 AM.

  6. #6
    Valued Forum Contributor
    Join Date
    12-22-2015
    Location
    HK
    MS-Off Ver
    2010
    Posts
    532

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

    try:
    Please Login or Register  to view this content.

  7. #7
    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 ikboy,
    Thanks for your reply. but, it's not working. could you please help me to find the solution by improving initial code. It's working with some scenarios. but, it's not validated with "2019/09/21", "2019-08-23", "2/08/018" and "2/9/2018".

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

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

    Quote Originally Posted by inoka View Post
    "2019/09/21", "2019-08-23", "2/08/018" and "2/9/2018".
    Please Login or Register  to view this content.

  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)

    Thanks jindon.
    But, It's not fulfilled my requirement. I want following validations.
    * Date format should be in "DD/MM/YYYY"
    * "DD-MM-YYYY" should be invalid format
    * "DD.MM.YYYY" should be invalid format
    * "D/M/YYY" should be invalid format
    * "YYYY/MM/DD" should be invalid format
    * "YYY/MM/DD" should be invalid format
    * "YYYY-MM-DD" should be invalid format
    * "YYYY.MM.DD" should be invalid format

    Should be accepted only the "DD/MM/YYYY" format as valid format. all other formats should be rejected. Also, date and month should be validated for number of days "30" and "31". In addition to that February month only for "28" and "29" days.

    If you could enhance following code, it will be easy. because, already it has validated some validations. Thank you so much for your great help.

    Function ValidateDate(ByVal pvDateCode) As Boolean

    Dim D As Integer
    Dim LeapYear As Boolean
    Dim M As Integer
    Dim Result As Boolean
    Dim Y As Integer, i As Integer
    Dim sDte As String, sPart As String

    'If Not IsNumeric(pvDateCode) Then Exit Function

    sDte = Format(pvDateCode, "dd/mm/yyyy")
    i = InStr(pvDateCode, "/")
    If i = 0 Then
    MsgBox "Invalid date"
    Else
    D = Left(sDte, i - 1)
    sPart = Mid(sDte, i + 1)
    i = InStr(sPart, "/")
    M = Left(sPart, i - 1)

    i = InStrRev(sDte, "/")
    Y = Mid(sDte, i + 1)
    End If

    If M > 12 Then Result = False: GoTo endit
    If Y < 1900 Then Result = False: GoTo endit

    LeapYear = (Y Mod 100 <> 0 And Y Mod 4 = 0) Or (Y Mod 400 = 0)

    Select Case M
    Case 1, 3, 5, 7, 8, 10, 12
    If D >= 1 And D <= 31 Then Result = True
    Case 2
    If LeapYear Then
    If D >= 1 And D <= 29 Then Result = True
    Else
    If D >= 1 And D <= 28 Then Result = True
    End If
    Case 4, 6, 9, 11
    If D >= 1 And D <= 30 Then Result = True
    End Select
    'endit:
    'If Not Result Then MsgBox "Format: dd/mm/yyyy"
    'ValidateDate = Result

    endit:
    ValidateDate = Result

    End Function

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

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

    Then change the pattern
    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)

    Thanks jindon.

    It's not working. could you please send me excel file.

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

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

    It's not working.
    This doesn't help at all.

    How is it not working???

    It is working for me.

    YOU attach your file, so that I can install.

  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)

    Thanks for your reply.

    When user enter above mentioned invalid date format, it is required to shown an error message.

    But, this is not fulfilled that feature. However I have attached my excel file in below.
    Attached Files Attached Files

  14. #14
    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,

    Could you please help me to improve following attached excel sheet for my validation. It has included debug error examples. I need to allow only for the "DD/MM/YYYY" date format.
    Highly appreciate your response and Thank you.
    Attached Files Attached Files

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

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

    So, you didn't know how to adjust...

    Change any cell(s) in Col.A, B
    Please Login or Register  to view this content.
    Attached Files Attached Files

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

    Thank you so much. It;s working. . could you please explain me, from where it is maintained column name.
    ex: If i need to validate it for columns "D","E",and "G", where do I need to change?

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

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

    Try change to
    Please Login or Register  to view this content.

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

    Thank you so much for your reply. I can not understand what is the method to apply it for desired column. I need to apply it for following columns.

    M, T, U, Z, AB, AC, AD, AH, AI, CS, CT

    Also, I need to validate it only 2 numbers for Date and 2 numbers for Month. and 4 numbers for Year. (DD/MM/YYYY)

    If you can upload excel sheet with these validation, it will be great help for me.
    Last edited by inoka; 12-14-2017 at 03:00 AM.

  19. #19
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

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

    Also, I need to validate it only 2 numbers for Date and 2 numbers for Month. and 4 numbers for Year. (DD/MM/YYYY)
    What's wrong with the current code?
    You are really confusing us, it only allows dd/mm/yyyy entry as you requested.

    If you are not able to adjust the code, you should ask everything in the first place.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  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)

    Thank you so much. Great... it's working.
    Sorry for inconvenience.

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

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

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

  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)

    There's a some issue. When I enter data value with "0", automatically, it is discarded initial "0" value. Then, it is shown as one digit number and it is displayed as error.

    Ex: when I add date as 04/06/2018, automatically excel converts it into 4/6/2018 and it is shown as invalid data.
    When I add date as 23/04/2019, automatically excel converts it into 23/4/2019 and it is shown as invalid data.

    Everything is working properly, how do I avoid this automatic conversion.
    Last edited by inoka; 12-14-2017 at 07:50 AM.

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

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

    That means the cell is formatted as d/m/yyyy.

    Reset the cell format as STRING.

  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)

    Thank you so much. It's working properly...

  25. #25
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

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

    ..............................
    Quote Originally Posted by jindon View Post
    If that takes care of your original question, select Thread Tools from the menu link above and mark this thread as SOLVED.

+ 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. Check Date Format Input into Excel Column with VBA code
    By stanlelma in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 01-20-2024, 03:27 AM
  2. [SOLVED] Text box validation - date entry - format "dd mm yyyy"
    By nigelog in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-25-2017, 09:01 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