How does read range works on date columns in excel?

Hi there,

I am configured my Region and Time as below:

So, with that I will be having dates in the excel format as dd/MM/yyyy

Now, I am reading that excels using Read range, returning the date values in the date table as MM/dd/yyyy hh:mm:ss and some times dd/MM/yyyy hh:mm:ss.

So, why this inconsistency with Read range either it should give any one of the above formats only. But not in this case…

Any ideas on this?

Hi @Robotman

Check the preserce format option in read range

It will resolve the error

Regards

Nived N :robot:

Happy Automation :relaxed::relaxed::relaxed:

1 Like

Not resolved the problem. Now it is reading as #######

Hi @Robotman

You can try this after read range

use this invoke code with dt1 as in/out argument

dt1.AsEnumerable().ToList().ForEach(Sub(row) row(columnname)=row(columnname).ToString)

It will convert the date column to text format in the given format and then based on later use u can use it for converting to date.

Regards,

Nived N

Happy Automation

Thanks. But I am just wondering about the behavior of read range on date format values.

Hi @Robotman

It is problem with the excel file i think , some formatting issues with cells

Regards,

Nived N

Any specific issue with excel file. I could see the same dates and format in the both excels (.xls) but read range exhibiting different behavior.

Hi @Robotman

correctly don’t know, mostly i face the same issue with excel files like showing #######

So i convert all data to text format then output it

it worked fine

further any ideas?

@Robotman
Sometime if date length is long then it shows #######…
Just open the excel and select all the data and under Format click Auto width option

1 Like

Yes, but I wish to know the Read range behavior here.

It will read the same format in Excel…

If you still get the mismatch in date format then refer below link