Converting object to MM/dd/yyyy

Hi all,
Hope someone can solve this:

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…

Thank you!

Hi @hp321

Could you try this

CDate(ObjectVariable).ToString("MM/dd/yyyy")

Cheers

Thanks @rikulsilva ,
I assigned the above to a string variable and got the following:

@hp321

Try this

CDate(ObjectVariable.ToString).ToString("MM/dd/yyyy")

Thanks @rikulsilva ,
Getting this…
image
Do you know what else I can try?

@hp321

From the error image, your CellVal variable is empty

You can first check if the CellVal is empty before converting

Please note that I first have an IF activity. the Assign is if the cell is not empty…[the assign is in the Else section]

Thanks @rikulsilva

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)

1 Like

Thanks @ppr ,
but is the error I’m getting with this invoke code connected to the above?
image
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

dt_auth=dt1

Hello, can you try Modify date activity

in general we would run for a different code / approach try to step away from invoke code

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

Thank you @ppr !
I modified the expression and it worked…

this description differs from above code lines

  • above code is adding for all

an empty row to dt1

However we can take

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):
grafik
the quick parsing
And the quick check
grafik

Test series:
grafik

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

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.