After reading excel table date row is becoming double values

Hey everyone,

I have a excel table which I read using read range. There is 1 column which contains dates, but when I read the excel that date columns values become “double” as in 0,48950231481 etc. even though it contains values like 12:22:20 PM.

any suggestions?

PS: there 50.000 rows inn the excel so for each row is not an option.

Hi !
There is an option in read range, preserve format, if you check it does it help ?

Can you share a screenshot of read range property?

image

Try suggestion by @Hiba_B

Yes preserve format does work but like i said there are too many rows, so it takes 1 hour for it to read if I preserve format

Then try read range by workbook once

Do you mind sharing your excel file if it is not sensitive data ?
Do you build a datatable in your workflow that could change the format ?
Could you check the format cell before and after reading the file ?

Sadly I cannot share the file, I dont build the dt, I directly read it from excel.

I have read a method Datetime.FromOADate(Convert.ToDouble(row(""))) to convert back to date format but I believe this is supposed to be used in a for each row loop, I cannot use for each due to excessive amount of rows.

Ok then what about you create a new excel file with two or three lines from your original excel file, removing all the columns, keeping only the date column.

Try to read that new excel file to see if the format changes (from date to double). The goal is to know if there is a problem with the original file, the cell data or the column data.

If the problem still occurs, with only a file with three dates it should be alright to share it right ?

When we have an excessive number of rows we could use a linq statement, not my speciality but maybe @ppr does have an idea ?

I’ve tried and it still happens, I am sharing the date column excel. New Microsoft Excel Worksheet (3).xlsx (8.1 KB)

I already face with same problem. I used read range after that use write range activity to different sheet. You can read range again newly created sheet. Then you can access real value. It seems not logical but it works.
:slight_smile:

on a first step, you already found the conversion approach. But the more important is what you want to do after readin the data. As this is more important

I read this table then I join it with another table then write it to another excel basically :), but the time values changes to double values when read.

edit: maybe somehow using “Datetime.FromOADate(Convert.ToDouble(” on a linq may be the solution?

so first strategy is to use the oadate function within a LINQ and fromulating the join

Options for datacolumn conversions you can find here (second option)

for helping you on the LINQ we do know all details (second datatable, expected output etc) Thanks

1 Like