validating date in excel VBA

[sankar_bhatta]

Hi All,

i am a newbie to VBA and find thread on this forum very helpful. I am developing a macro to validate soem fields in excel. one of the column in this excel can haev a date type of data( please note the format of this field is not date ).
I am reading the whole data of the excel into a array of type VARIANT. i am then passing the specific columns to another sub where I am using IsDate function to validate if this ia date. But the function IsDate is always returning false

I tried CDate etc. but nothing seems to work



Sub Validate_Contracts_EffectiveDate(par1 As Variant)
If IsDate(par1) = False Then
lv_error = True
lv_err_text = "only date format is allowed"
End If
end Sub

 

[Tom Urtis]

Just thinking out loud, it might be that what you think is a valid date entry is not what Excel thinks is a valid date entry. For example, if in some cell you enter
May 5 2021
Excel will regard that as a text entry no matter what verification function you try to use.

Or there might be some other factor at play, such as a cell formatted beforehand as text, or a stray invisible character in the cell. Post back with an exact (exact means exact) example of a cell value in your workbook that is believed (by looking at it) to be a date, and maybe someone will notice what's wrong or recommend an alternative verification method.

 

[sankar_bhatta]

Just thinking out loud, it might be that what you think is a valid date entry is not what Excel thinks is a valid date entry. For example, if in some cell you enter
May 5 2021
Excel will regard that as a text entry no matter what verification function you try to use.

Or there might be some other factor at play, such as a cell formatted beforehand as text, or a stray invisible character in the cell. Post back with an exact (exact means exact) example of a cell value in your workbook that is believed (by looking at it) to be a date, and maybe someone will notice what's wrong or recommend an alternative verification method.

this is how the excel column looks like. I have entered some valid dates and some invalid dates like 45.03.2021. In my code, I am first reading all the data in the excel into an array of type variant

iRowsCount = lv_ws.Range("A" & lv_ws.Rows.Count).End(xlUp).Row
'
Set rngSource = lv_ws.Range("A2:AN" & iRowsCount)
Set file_Ctr_Columns = lv_ws.Range("A1:AN1")
gv_column_no = rngSource.Columns.Count
ReDim gv_ctr_tab(1 To rngSource.Rows.Count, 1 To rngSource.Columns.Count)

gv_ctr_tab = rngSource.Value

here gv_ctr_tab contains all the data of the all cells

then i am calling a annother sub passing individual cells to validate

Call Validate_Custom_Fields(gv_ctr_tab(x, y), lv_column_name, lv_cus_count)

with in this sub I have sevral subs which validate the data of each cell depending on type of data

Sub Validate_Custom_Fields(par1 As Variant, par2 As String, par3 As Integer)
'Par1 is value of column in contract template excel
'Par2 is column name
'Par3 is row number of cusomt field in Custom_fields tab

Dim lv_string As String
'List values
lv_string = gv_cust_tab(par3, 3)

'Field type
Select Case gv_cust_tab(par3, 2)

Case "Boolean"

Call Validate_Boolean(par1, par2)

Case "Date"

Call Validate_Date(par1, par2)

Case "ValueList"

Call Validate_ListValues(par1, par2, lv_string)

Case "Numeric"

Call Validate_Numeric(par1, par2)

End Select


End Sub

the problem is with in Validate_Date sub

Sub Validate_Date(par1 As Variant, par2 As String)


Dim lv_err_text As String
Dim lv_error As Boolean
Dim i As Integer
Dim lv_date_string As String
Dim lv_date As Date

lv_error = False

If IsDate(par1) = True Then
Else
lv_error = True
lv_err_text = "Please enter valid date for this custom field"
End If



End Sub

 

[sankar_bhatta]

Hi Experts,

Any Ideas or suggestions for me?

 

[Tom Urtis]

When I enter in my Excel worksheet these examples such as you posted...
in cell A1, 12.03.2021
in cell A2, 03.31.2021
in cell A3, 12.03.2121
...these are all text entries, as I suspected when I first wrote:
"it might be that what you think is a valid date entry is not what Excel thinks is a valid date entry."

I do not know what you are actually doing or what you want. The next entry in your pictured example is
45.03.2021
and that is a text value regardless of a date format being mmddyyy or ddmmyyyy.

So maybe your task at hand is to determine if such an entry is or is not a date if it could be formatted as a date. Recall that formatting a cell only affects how the entry looks to the human eye; it does not change the underlying cell value.

You are still left with the precedent task of changing those intervening period (or dot) characters to a forward slash or a dash character, which, when adding a zero to that, will change that item to a bona fide date, if the entry can possibly be a date.

To do that with a formula, for example, can be this:

In column AB In a different column enter: Returning
1 cus_Segmentation
2 12.03.2021 =SUBSTITUTE(AB2,".","/")+0 44533
3 03.31.2021 =SUBSTITUTE(AB3,".","/")+0 44286
4 12.03.2121 =SUBSTITUTE(AB4,".","/")+0 81057
5 45.03.2021 =SUBSTITUTE(AB5,".","/")+0 #VALUE!
6 12.34.2021 =SUBSTITUTE(AB6,".","/")+0 #VALUE!
7 12.03.2021 =SUBSTITUTE(AB7,".","/")+0 44533

From there it is a simple matter of selecting the formula cells and formatting them in your desired date format, and probably paste special them as values too.

The issue remains as to what you want to do about the #VALUE! returns, maybe delete those records (rows) but that it is up to you.

To do it using VBA, using column AB as the location of the date-like original values in your picture, can be this macro:

Sub ConvertToDate()
Dim cell As Range, strCell As String
Application.ScreenUpdating = False
For Each cell In Range("AB2:AB" & Cells(Rows.Count, 28).End(xlUp).Row)
With cell
strCell = .Value
.Value = WorksheetFunction.Substitute(strCell, ".", "/")
If IsDate(.Value) = False Then .Value = .Value & " is not a date."
End With
Next cell
Columns(28).AutoFit
Application.ScreenUpdating = True
End Sub

You will end up with this:

In column AB
1 cus_Segmentation
2 12/3/2021
3 3/31/2021
4 12/3/2121
5 45/03/2021 is not a date.
6 12/34/2021 is not a date.
7 12/3/2021

 

[sankar_bhatta]

When I enter in my Excel worksheet these examples such as you posted...
in cell A1, 12.03.2021
in cell A2, 03.31.2021
in cell A3, 12.03.2121
...these are all text entries, as I suspected when I first wrote:
"it might be that what you think is a valid date entry is not what Excel thinks is a valid date entry."

I do not know what you are actually doing or what you want. The next entry in your pictured example is
45.03.2021
and that is a text value regardless of a date format being mmddyyy or ddmmyyyy.

So maybe your task at hand is to determine if such an entry is or is not a date if it could be formatted as a date. Recall that formatting a cell only affects how the entry looks to the human eye; it does not change the underlying cell value.

You are still left with the precedent task of changing those intervening period (or dot) characters to a forward slash or a dash character, which, when adding a zero to that, will change that item to a bona fide date, if the entry can possibly be a date.

To do that with a formula, for example, can be this:

In column AB In a different column enter: Returning
1 cus_Segmentation
2 12.03.2021 =SUBSTITUTE(AB2,".","/")+0 44533
3 03.31.2021 =SUBSTITUTE(AB3,".","/")+0 44286
4 12.03.2121 =SUBSTITUTE(AB4,".","/")+0 81057
5 45.03.2021 =SUBSTITUTE(AB5,".","/")+0 #VALUE!
6 12.34.2021 =SUBSTITUTE(AB6,".","/")+0 #VALUE!
7 12.03.2021 =SUBSTITUTE(AB7,".","/")+0 44533

From there it is a simple matter of selecting the formula cells and formatting them in your desired date format, and probably paste special them as values too.

The issue remains as to what you want to do about the #VALUE! returns, maybe delete those records (rows) but that it is up to you.

To do it using VBA, using column AB as the location of the date-like original values in your picture, can be this macro:

Sub ConvertToDate()
Dim cell As Range, strCell As String
Application.ScreenUpdating = False
For Each cell In Range("AB2:AB" & Cells(Rows.Count, 28).End(xlUp).Row)
With cell
strCell = .Value
.Value = WorksheetFunction.Substitute(strCell, ".", "/")
If IsDate(.Value) = False Then .Value = .Value & " is not a date."
End With
Next cell
Columns(28).AutoFit
Application.ScreenUpdating = True
End Sub

You will end up with this:

In column AB
1 cus_Segmentation
2 12/3/2021
3 3/31/2021
4 12/3/2121
5 45/03/2021 is not a date.
6 12/34/2021 is not a date.
7 12/3/2021

HI,

Thanks for the reply. Does it mean IsDate only take values which are in the format of xx/xx/xxxx?

 

[Tom Urtis]

No.