Check date string value with IsDate

[mikeymay]

I have a list of dates strings entered as dd.mm.yy

These dates are added into an application that doesn't perform the validity of the entry and then exported into the file I am checking.

I need to check the entries to ensure they reflect an actual date, e.g. 32.01.21 will be flagged as invalid.

I thought by replacing the . character with a / character would work but the new value is interpreted as 22nd Jan 2032.

I have tried using DateSerial but this returns 1st Feb 2020.

If anyone can suggest a solution that doesn't required the original entry to be entered with a / instead of a . it would be appreciated.


Thanks

 

[GraH]

Maybe try

 =Isnumber(Substitute(Date, ".", "/")+0)

 

[mikeymay]

Sorry I should have stated that this check is happening via a VBA prcedure

 

[Akuini]

I assumed all entries (valid date or not) are text not date.
Try:

Sub a1159782a()
'https://www.mrexcel.com/board/threads/check-date-string-value-with-isdate.1159782/
Dim i As Long
Dim tx As String
Dim va, vb, a

va = Range("A2", Cells(Rows.Count, "A").End(xlUp))
ReDim vb(1 To UBound(va, 1), 1 To 1)
For i = 1 To UBound(va, 1)
    vb(i, 1) = False
    tx = va(i, 1)
    
    If tx Like "##.##.##" Then
    a = Split(tx, ".")
        If a(0) < 32 And a(1) < 13 Then
            If Format(DateSerial(a(2), a(1), a(0)), "dd") = a(0) Then
                vb(i, 1) = True
            End If
        End If
    
    End If
Next

Range("B2").Resize(UBound(vb, 1), 1) = vb
End Sub

Book2
	A	B	C	D	E
1	DATE
2	32.01.21	FALSE
3	03.12.23	TRUE
4	45.23.12	FALSE
5	47.08.21	FALSE
6	30.02.22	FALSE	February can't have 30
7	1.9.20	FALSE
8	10.14.22	FALSE
9	07.11.09	TRUE
Sheet1

 

[mikeymay]

Thanks Akuini

The specific issue I had, which I should have stated, is a date of 31.11.20 be entered.

also when an invalid date is detected I have a user form to pop up to ask for the correct date. So rather than returning a boolean, I need to leave a correct date, albeit using a . rather than a /, to stay in the cell and where invalid, the date entered via the userform replaces the incorrect date but in the format using a . as a seperator rather than a /.

 

[Akuini]

I need to check the entries to ensure they reflect an actual date, e.g. 32.01.21 will be flagged as invalid.

I don't quite understand what you want, I thought you you wanted to identify the invalid dates.
Do you mean you want to enter in format dd.mm.yy then use macro to convert it to dd/mm/yy?