String was not recognized as a valid DateTime

Help
, , ,
1

Hi, I have a list of time in a column called “Requested On” and I keep getting this error saying that
“Assign count: String was not recognized as a valid DateTime.” where my activity “Assign count” is getting this error in my workflow. I have completely no idea where and why this error came from.

Here is my xaml file and excel file:
Book1.xlsx
test.xaml

I appriciate if anyone can help me with this.

2

@Lau_Wei_Ting

Could you please show me screenshot of what expression are you used here ?

3

dt = input.AsEnumerable.Where(Function(r) datetime.ParseExact(r(“Requested On”).ToString.Trim,“M/d/yy h:mm:ss tt”,nothing) >=date_before And datetime.ParseExact(r(“Requested On”).ToString.Trim,“M/d/yy h:mm:ss tt”,nothing)<=date_now).CopyToDataTable

This is my “Assign Count” activity expression

4

I am getting this when executingimage

5

Hi
Hope theses steps would help you resolve this
—let’s take like we have read this excel with READ RANGE activity and obtained the output with a variable of type datatable named dt
—now use FOR EACH ROW activity and pass the above variable as input
—inside the loop use a assign activity like this
str_input = Split(row(“yourcolumnname”).ToString,” “)(0).ToString.Trim

—then another assign activity like this
Str_input = Split(str_input,”/“)(0).padleft(2,CChar(“0”))+”/“+ Split(str_input,”/“)(1).padleft(2,CChar(“0”))+”/“+ Split(str_input,”/“)(2)

—then inside the same loop a final assign activity like this
dat_value = DateTime.ParseExact(str_input,”MM/dd/yy”,System.Globalization.CultureInfo.InvariantCulture)
Where dat_value is a variable of type DateTime

Which we can use for filtering

Cheers @Lau_Wei_Ting

6

for this, because I am using read CSV activity however I send the file here as xlsx type as here does not allow me to upload CSV file type. U may need to convert the file type to CSV when testing it.
Sorry for the trouble. :sweat_smile:

7

Hi, I am still getting the error…
Any other alternative way I can resolve it?

8

Hmm
May I know the issue we were facing
@Lau_Wei_Ting

9

the issue happen is at the activity “Assign count”. My intention is to filter any dates that within 6 months count from today date.

image
image703×220 10.4 KB

This is my original expression: input.AsEnumerable.Where(Function(r) datetime.ParseExact(r(“Requested On”).ToString.Trim,“M/d/yy h:mm:ss tt”,nothing) >=date_before And datetime.ParseExact(r(“Requested On”).ToString.Trim,“M/d/yy h:mm:ss tt”,nothing)<=date_now).CopyToDataTable
This expression work fine when my Requested date is this
image
However, I input another source file which “Requested On” Column display this
image
I had change the Date method accordingly thus this error occur.
dt = input.AsEnumerable.Where(Function(r) datetime.ParseExact(r(“Requested On”).ToString.Trim,“M/d/yy h:mm:ss tt”,nothing) >=date_before And datetime.ParseExact(r(“Requested On”).ToString.Trim,“M/d/yy h:mm:ss tt”,nothing)<=date_now).CopyToDataTable

I had no idea why this error is coming out and how does this error came out since the DateTime is matching according to the xlsx file.

I had tried my best to explain, hope that u can understand the issue I am facing :sweat_smile: