Conversion of written-out date to DateTime?

Help
,
1

I have an Excel document that has dates listed in cells as “January 1, 2020” where the month is written in English as a string value.

Question: Is there a way to parse this string into a Date type in the format “MM/dd/yyyy” without having to split the string and do any major manipulation? Preferably I am hoping something like the below would be possible.

“DateTime.ParseExact(row(0).ToString,“MM/dd/yyyy”,System.Globalization.CultureInfo.InvariantCulture)”

Thanks!

EDIT: I have resolved my own issue, after some more digging, it turns out that these string formats can first be converted using

“Convert.ToDateTime(row(1).tostring).Date.ToShortDateString”

This returns a date in the format “MM/dd/yyyy” which was my ultimate goal!

Thank you all for the help!

2

try with format as: “MMMM d, yyyy”

2 Likes
3

Hi
Once after getting the datatable from excel with READ RANGE activity and a datatable named dt pass that as input to FOR EACH ROW activity
Inside that loop use a first assign activity like this with expression

str_input = Split(row(“yourcolumnname”).ToString,” “)(0).ToString+” “+IF(Split(row(“yourcolumnname”).ToString,” “)(1).ToString.PadLeft(3,CChar(“0”))+” “+ Split(row(“yourcolumnname”).ToString,” “)(2).ToString

Then use a assign activity for DateTime parsing
Like this
row(“yourcolumnname”) = DateTime.ParseExact(str_input.ToString,“MMMM dd, yyyy”,System.Globalization.CultureInfo.InvariantCulture)

The reason for the first assign activity is sometimes it may come as
January 1, 2020
Or
January 12, 2020

So to handle that it was included

Cheers @sean.finn

1 Like