convert date in excel from dd-mm-yyyy to dd/mm/yyyy format and to check whether a date is valid or not

Question

~~I'm having a hard time in converting the date from dd-mm-yyyy format to dd/mm/yyyy format.~~

For Example, When i enter a date in excel as 25/02/2012 (dd/mm/yyyy), after entering the date if go in the next line it converts the date in the 25-02-2012 (dd-mm-yyyy) format.

what i want to do is that when i enter the date in (dd/mm/yyyy) format in excel it should keep it as it is and should not change it back to (dd-mm-yyyy) format when i go the next cell.

when i enter my date as the current system date my code gives me an error, i am having trouble validating the date i.e. is the date entered is a valid date or not

    Sub valid_date()
    ' ---------------------------------------------------------------------
    ' Final Code - Trial
    ' ---------------------------------------------------------------------

    Dim d1 As Variant
    Dim IssueDate As Variant
    Dim str As Variant

        d1 = Worksheets("Sheet1").Cells(6, 1).value

            MsgBox " The Issue Date format is " & d1

        sysdate = Date

        MsgBox "System Date is " & sysdate


                If IsDateValid(d1) Then ' if date is in dd/mm/yyyy format then print this
            If (d1 > sysdate) Then
                MsgBox "Invalid date"
            End If
        End If
     End Sub

    Function IsDateValid(pdate) As Boolean

        IsDateValid = False

        Set RegExp = CreateObject("VBScript.RegExp")

        '   it only matches whether date is in dd/mm/yyyy format or not
        '
        '   [1-9]|0[1-9]|1[0-9]|2[0-9]|3[0-1]   ---> it allows the DATE  from 01 to 31
        '   [1-9]|0[1-9]|1[0-2]                 ---> it allows the MONTH from 01 to 12
        '   1[9][0-9][0-9]|2[0][0-9][0-9]       ---> it allows the YEAR  from 1900 to 2099
        '
        '   below is the regular expression for checking the date in dd/mm/yyyy format

        RegExp.Pattern = "^([1-9]|0[1-9]|1[0-9]|2[0-9]|3[0-1])[/]([1-9]|0[1-9]|1[0-2])[/](1[9][0-9][0-9]|2[0][0-9][0-9])$"

        ' check whether the date is in dd/mm/yyyy format or not....
        tempdate = RegExp.Test(pdate)

             If tempdate Then    ' if tempdate is in dd/mm/yyyy format than proceed further
                'If isdate(tempdate) Then  ' if date is a valid date then proceed further
                 If isdate(pdate) Then
                    IsDateValid = True
                Else
                    IsDateValid = False
                End If
            Else
                IsDateValid = False
            End If               
    End Function

i'm using the above mentioned code by using a regular expression to check whether the date is in dd/mm/yyyy format or not

~~but the problem which i'm facing is that it takes the date in excel as dd-mm-yyyy format whenever i enter the date in dd/mm/yyyy format.~~

i have updated my code a bit, i also need one more help when i enter my date as the current system date it gives me error

for example, when i enter my date as 09/09/2012 (suppose this your current system date) and when i check this date using IsDate method, it gives me an error

i have again edited my code,

Can anyone please help me on this

Zairja Zairja · Accepted Answer · 2012-08-09T18:00:50

You don't need VBA/RegEx. Select the cells/columns where you input dates and create a Custom number format: dd/mm/yyyy. Now no matter how you type in a valid date (05-05-2000, 3-1-2010, 14/6-1990, etc.), it should be formatted as dd/mm/yyyy.

And, as Olle points out, you should use the Date object rather than Variant if you are going to be manipulating dates in VBA. This way you're working with the serial number and not a string with potential formatting issues.

convert date in excel from dd-mm-yyyy to dd/mm/yyyy format and to check whether a date is valid or not

3 Answers