I’m looping through an excel sheet and extracting the dates from the appropriate column as an Object variable.
However,when writing it to a different sheet the date shows like this: 01/29/2023 00:00:00 instead of showing only the date.
I imagine I need to create a new variable to store the correct output. and convert it first to datetime type and then to string with “MM/dd/yyyy”
note that the sheet contains thousands of rows…
Please advise…
isNothing - checking if the value is null
But still a value can be empty - empty String
we check isNothing(CellVal) OrElse String.IsNullOrEmpty(CellVal.ToString.Trim)
Thanks @ppr ,
but is the error I’m getting with this invoke code connected to the above?
Does anyone know what causes the error?
code below: [keeping only rows that the date column is N/A or null or are before 1980 or after 2100:]
Dim dt1 As DataTable
dt1=dt_auth.Clone
For Each row In dt_auth.AsEnumerable
Dim newrow As datarow=dt1.NewRow
If row(5).ToString.trim=“” Or row(5).ToString.trim=“N/A"Then
newrow.ItemArray=row.ItemArray
dt1.rows.add(newrow.ItemArray)
Else
If system.text.RegularExpressions.regex.ismatch(row(5).ToString.trim,”\d{4}") Then
If CInt(datetime.ParseExact(row(5).ToString.Trim,“MM/dd/yyyy hh:mm:ss”,system.Globalization.CultureInfo.InvariantCulture).ToString(“yyyy”))<1980 Or CInt(datetime.ParseExact(row(5).ToString,“MM/dd/yyyy hh:mm:ss”,system.Globalization.CultureInfo.InvariantCulture).ToString(“yyyy”))>2100 Then
newrow.ItemArray=row.ItemArray
dt1.rows.add(newrow.ItemArray)
End If
End If
End If
Next
Thanks @ppr ,
Do you have a different idea how I can keep rows that the DateColumn is one of the below:
*empty
*N/A
*before the year 1980
*after the year 2100
and understand it as filter case.
Also we can extend and additional keep all rows where a non empty row(5) value cannot be parsed into a datetime
Kindly note:
a value like 123456 will match the isMatch
values like 17/12/2004 also will match the isMatch
Both above are not dates within the format: MM/dd/yyyy HH:mm:ss
We keep in mind the following (as currently only default format were within the focus):
the quick parsing
And the quick check
Test series:
So we can setup a filter LINQ
Assign Activity:
dtFiltered =
(From d In dt_auth.AsEnumerable()
Let chk1 = isNothing(d(5)) OrElse String.IsNullOrEmpty(d(5).toString.Trim)
Let v1 = If(chk1, "01/01/0001 00:00:00",d(5).toString.Trim)
Let chk2 = DateTime.TryParse(v1, Nothing)
Let v2 = If(chk2, v1, "01/01/0001 00:00:00" )
Let dpy = DateTime.Parse(v2).Year
Where dpy < 1980 OrElse dpy > 2100
Select r = d).CopyToDataTable
and exploited that we can set all non parseable dates to the default date as default date’s year < 1980