How to get the Excel value alone, if the cell contains formula

Help Studio
, ,
1

In an Excel file, one of the “ColDate” column contain formula and we need to extract the value of it.
Formula=now()-54/24
Value = 9/1/24 10:59 Am
Expected output = 9/1/24 10:59 Am
When tried in for each datatable, Currentrow.item(“ColDate”).Tostring
getting Value = 45536.4407
Tried with Copy range,
if select Value in the properties getting Column Value= 45536.4407
if Select Formula in the properties getting Column value= now()-54/24
Expected output = 9/1/24 10:59 Am

Any solution

2

Number looks like a OADate value
grafik

Maybe the read range workbook activity was used as often it retrurns Date back as OADate values

Give a try at
grafik

3

Tried by selecting the option “PreserveFormat”, but it doesn’t worked

4

just share with us what was done (e.g Screenshot from the modellings), if possible also share the Excel with us

5

Hi @kalpana_venkutusamy2

Have you ever tried using Excel activities aside from workbook activities?

6

What is column data type in the excel ?
If possible try to change the column data type to date time or string and see what results you are getting.
If manual setting can not be done
You can add one line script to change column data type before for each loop starts

7

Hi @kalpana_venkutusamy2

Please enable the PreserveFormat option in Properties it will help you .

Thanks,
Purushotham