Check Cells for Real Dates

[rhombus4]

Whats the easiest way to check if a cell is a valid date before doing something else

Need to Check A7, C7 L7 and Z7 if any are either text or incorrect date entries exit sub with a message box

Thanks

 

[jasonb75]

It depends on your opinion of a 'valid date'

One thing that comes to mind

Select Case False
    Case IsDate(Range("A7").Value), _
         IsDate(Range("C7").Value), _
         IsDate(Range("L7").Value), _
         IsDate(Range("Z7").Value)
        MsgBox "error"
        Exit Sub
End Select

But in my opinion, it is not a perfect solution.

 

[rhombus4]

It depends on your opinion of a 'valid date'

One thing that comes to mind

Select Case False
    Case IsDate(Range("A7").Value), _
         IsDate(Range("C7").Value), _
         IsDate(Range("L7").Value), _
         IsDate(Range("Z7").Value)
        MsgBox "error"
        Exit Sub
End Select

But in my opinion, it is not a perfect solution.

It depends on your opinion of a 'valid date'

the cells are already formatted as a date so if they enter 15/12/21 or 15-12-21 or 15/12 the cell would be 15 Dec 21 which is fine
but if they enter 15.12.21 it enters as 15.12.21 or 15 enters as 15 Jan 00 which I don't want. Also it cant be text.

PS if I just wanted to check one cell, i.e. A7, instead of Select Case WOuld it just be

if IsDate(Range("A7").value = False
then Msgbox "error"
Exit Sub

 

[jasonb75]

or 15 enters as 15 Jan 00 which I don't want

Apart from that one, it will work as needed.

That would be evaluated as 15/01/1900 which is technically a valid date.

If the dates are being entered by the user before the code runs then applying data validation to those cells with a specified start and end date will be much easier. Then you can simply exit the code if any of the cells are empty (assuming that they must all be completed).

 

[rhombus4]

Thanks

Didn't want to use data validation as entries can cover a few thousand rows. I'll try the code tomorrow

 

[jasonb75]

With the code, you will need something like this to avoid the 15 Jan 00 type of entry. The reference date can be adjusted as needed if you need valid dates to go back earlier than 2015.

Dim mDate As Date
mDate = #1/1/2015#
If Not IsDate(Range("A7").Value) Or Range("A7").Value <= mDate Then
        MsgBox "error"
        Exit Sub
End If

 

[rhombus4]

Thanks Jason,
Using the above how do I combine A7, C7, L7 and Z7
Also forgot to mention that the user can leave one of the Cells Blank, so the <=mDate is good if they enter a really old date, but it also picks up blank as really old date

 

[jasonb75]

Also forgot to mention that the user can leave one of the Cells Blank

This will not check if more than one is empty, but it should be ok for the rest.

Dim mDate As Date, c As Range
mDate = #1/1/2015#
For Each c In Range("A7,C7,L7,Z7")
    If Not IsEmpty(c) Then
        If Not IsDate(c.Value) Or c.Value <= mDate Then
            MsgBox "error"
            Exit Sub
        End If
    End If
Next

Noting that you mentioned that it would be over 1000 or so rows, how do you intend to determine which row it should be looking at?
Intersect is one way that comes to mind, but how you identify the row to check and what you want to do next will dictate the best method.

 

[rhombus4]

Thanks, that seems to work

I'm using, Worksheet_SelectionChange(ByVal Target As Range) then add the row number to a Cell

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("D2")) Is Nothing Then
  Range("Z1").Value = Target.Row

although not sure how to combine the target row and Column i.e. to get Range("A7,C7,L7,Z7")

 

[jasonb75]

Something like this maybe? Where does D2 come into the process?

Application.EnableEvents = False
Dim mDate As Date
mDate = #1/1/2015#
If Not Intersect(Target,Range("A:A,C:C,L:L,Z:Z")) Is Nothing Then
    If Not IsEmpty(c) Then
        If Not IsDate(Target.Value) Or Target.Value <= mDate Then
            Range("Z1").Value = Target.Row
        End If
    End If
Next
Application.EnableEvents = True