Date showing in excel and in the data table are different and needs conversion

Hello Team,
I have situation where I have downloaded from a customer portal and the date that is showing in the excel format is DD-MM-YY
image
image
When I extracted the data in Read range then the date shown is in

DataRow { HasErrors=false, ItemArray=object[3] { “91295942”, “5003213532”, [03/22/2021 00:00:00] }, RowError="", RowState=Added, Table=[DataTable] }

I was converting from DD-MM-YYYY to MM-DD-YYYY as per the excel sheet column and this is the parse command I had written.

datetime.ParseExact(row.item(2).ToString,“MM-dd-yyyy hh:m:ss”,system.Globalization.CultureInfo.InvariantCulture).ToString(“MM/dd/yyyy”)

If I don’t add the .ToString then I get an error.

option strict on disallows implicit conversions from ‘object’ to 'string’

else

Message Box: String was not recognized as a valid DateTime.

Need your helping hand.

Regards,
Manjesh

Try with:

datetime.ParseExact(row.item(2).ToString,“MM-dd-yyyy HH:mm:ss”,system.Globalization.CultureInfo.InvariantCulture).ToString(“MM/dd/yyyy”)

If it’s not working try to write line one of that cell values with that: row.item(2).ToString

Hello @Yameso ,
I tried the first part still the same error as before

Message Box: String was not recognized as a valid DateTime.

I did not understand the second part. I have attached the excel file

TEST.xlsx (9.9 KB)

I am reading the file, add a empty data column Date column and convert the column GR Date into the format as mentioned above write it to the Date column.

Regards,
Manjesh

Hi @manjesh_kumar
just check this
Main.xaml (7.9 KB)

Formatting was sucessfull!

Regards,
Nived N
Happy Automation

Hello @NIVED_NAMBIAR ,

Thank you for that.

I realized that I had made a mistake in the format

datetime.ParseExact(row.item(2).ToString,“MM-dd-yyyy HH:mm:ss”,system.Globalization.CultureInfo.InvariantCulture).ToString(“MM/dd/yyyy”)

Instead of / I was using -

Thank you guys for the great support.

1 Like

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