How to extract cell value which has Date+integer value( i.e. date+2)

[ashwini.bagewadi]

Hi All,

I have an excel file which has formula in it i.e. =C2+2 and in C2 cell i have =TODAY() date, you can find the details in the excel file provided by me
Book1.xlsx (10.1 KB)
, when i read this cell by ticking preserve format Check box in datatable it is reading it in general format. Example in an excel file which contains a date 6/30/2021 after reading it, it is getting converted to general format 44377. You can check the output provided in output.xlsx file
output.xlsx (8.4 KB)
Please help me as to how i can solve this issue. Any guidance will be really helpful and appreciated.

Please find the attached workflow which i used for reading and writing the excel file.
BlankProcess2.zip (15.9 KB)

Thank you
Ashwini

[ppr (Peter Preuss)]

[ashwini.bagewadi]

Please can you elaborate, sorry i didnt understand from the screenshot

[ppr (Peter Preuss)]

if excel feeds 44377 to the datatable after read range the value can be converted to a dateTime by using the statement:
DateTime.FromOADate(44377) or DateTime.FromOADate(CInt(row(ColNameOrIndex).toString))

Your request was interpretated that you want to get the corresponding date from the number value, right?

[ashwini.bagewadi]

Thank you Peter for your reply
If you see Book1 excel file it has C2+2, is there a way to read it as date only, and not convert it later as your solution suggests it, because the excel file format is not fixed, later the excel might have different column header under which this fomula might have applied. To apply the statement i need column name or index which will be hardcoded, is there a dynamic way to do this?

[ppr (Peter Preuss)]

Sometimes readin excel is surprising wild related to the result. Same also on the differences when used the read range from workbook.

Give a try on read range, write range used along with the Excel Application scope. It made good results on my end test

[ashwini.bagewadi]

Thank you so much peter