Convert a string to Date format

hello,

I ran into another situation that i can’t figure out. i have an excel and on a column i have the date, that is in the format 13.01.2021 - the info is extracted from some PDFs. i need to change that into date format so that i can compare with another column and see difference in days.

The thing is, regardless of what I’m doing, i can’t make it to work. In short lines, i read the excel into a DT, introduced a “for each row”, but everytime i try to convert it ( Datetime.ParseExact(variable,“MM/dd/yyyy”,System.Globalization.CultureInfo.InvariantCulture) it doesn’t work.

As always, a suggestion in the right path would be much appreciated.

Thanks,
Cristi

Hi @Cristian_Ionita ,

Use like below and try. thanks.

Datetime.ParseExact(variable,“dd.MM.yyyy”,System.Globalization.CultureInfo.InvariantCulture).tostring(“MM/dd/yyyy”)

2 Likes

when readin an excle into a datatable the value within the datatable column can be different as in the excel. So we cannot rely on the format shown on the excel.

In such case we check it during a debugging

  • set a breakpoint
  • run a debug and get paused
  • use the immediatre panel and check with following statement
    yourdatatableVar.Rows(0)(YourColNameOrIndex)

feel free to check also other rows by using another row index (1,5…)

Once the used format is cleared the date conversion method can be adopted

2 Likes

thanks - it works - but probably because it’s late and basically forgot what i learned in the last month - how can i write it in the excel (or even replace the values in the same column). Cause for the death of me, i can’t think straight at this point :))

Hi @Cristian_Ionita ,

Store the Output of the date parse expression into different variable FInalDate and assign this value back to the same column by using the assign activity in the same for each loop. i hope it might helpful for your requirement. thanks

CurrentRow(“YourColumnNameWhichyougotvariable”) = FinalDate

As a simple explanation for anyone reading this, the DateParse argument is supposed to be the format of the string you want to convert, not the format you want to see it in. That’s why “MM/dd/yyyy” didn’t work when the string is in MM.dd.yyyy format.

2 Likes

This is not the solution. This still leaves the value as a string, and he wants to compare dates. They must be assigned to datetime variables, WITHOUT the .ToString on the end of the Datetime.Parsexact expression.

Hi @postwick ,

i agree with you it is not the complete solution. he wants to compare the date and find the difference between them. i suggested the way he can store back the format he wants in string format into data table and he needs to convert them to date by using cdate function and find the difference between them.

Hi @Cristian_Ionita if you want you can revert the solution marked above and follow the suggestion by @postwick and other forum members if you dont find complete solution for your problem. thanks.

guys, you’ve helped me a lot - both solutions help me in a way or another in the process that i’m trying to build. Thanks a lot for your time and explanations. learned something new :smiley:

noted and it explains why it didn’t work while i tried it. thank you very much

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