DateTime Formatting issue while reading the Excel

[ashutoshkhantwal]

Hi,

I have an excel which has a date time related column. When I read the excel the values are fetched in form of a string/double value having some random numbers like 43788.8488…

I tried to use FromOAdate for for matting the whole date column as well but then it gives an exception stating “input string was not in a correct format”.
The excel formatting is of the form as shown below.

dateChangeValue = DateTime.FromOADate(CDbl(row(“Verification Date”))) , this is how I have written the code.
I am iterating the same in a for each row.
Could anyone tell what is wrong in it?

[Palaniyappan (Forum Leader)]

@ashutoshkhantwal

dateChangeValue = DateTime.FromOADate(CDbl(row(“Verification Date”)))

Hi
this expression is actually correct
but i think the activity is taking the date value as it is
lets check that with a writeline activity where mention like this
row(“Verification Date”).ToString inside the for each row loop
lets see what value is coming in the output panel

Cheers @ashutoshkhantwal

[ashutoshkhantwal]

@Palaniyappan

but i think the activity is taking the date value as it is

Hello Palani,
The formatting is of the form as visible on the image and it is coming as it is in the datatable as well.

[J0ska]

Try to split the expression into two steps:
oaDate = CDbl(row(“Verification Date”))
dateChangeValue = DateTime.FromOADate(oaDate)

It will help you identify which conversion actually fails.

Cheers

[AshwinS2 (Ashwin S)]

Hi @J0ska

Try Cdate(row(“Verification Date”).ToString(“dd/mm/yyyy”))

Thanks
Ashwin S

[ashutoshkhantwal]

Hi, Ashwin. The excel format for the cell/column is not helping the case and it won’t get converted to date using cdate() because the value read by the data table is some garbage value instead of the date value. Also I have kept keep formatting as checked while reading. Could that be an issue?

[ashutoshkhantwal]

Will try with that. Thanks

[J0ska]

I tried to simulate your business scenario.
1/ I made an excel with date column
2/ I read excel using ReadRange activity

and I am getting already a string value in the resulting datatable

So the whole conversion seems redundant and the following should be sufficient

dateChangeValue = CDate(row(“Verification Date”))

Cheers

[ashutoshkhantwal]

I found out the issue why the code mentioned by you and myself wasn’t working. It was because there was a row which had a blank value for the date due to which it was giving an error “invalid string” . Just needed to delete that row and it worked.

Thanks everyone for their input.