Microsoft excel functions returns a wrong result

Question

Hi All,

I use this dll "Microsoft.Office.Interop.Excel" in order to evaluate my excel functions.

 public dynamic EvaluteFormula(string expressionFormula)
     {
         Application excelApp = new Application();
         return excelApp.Evaluate(expressionFormula);
     }

When I use TODAY/NOW function, I don't get the expected result instead I get a number, for example the result of :

• TODAY() is 44424.
• NOW() is 44424.613976967594

Has anyone ideas how to solve this problem ?

I encountered this problem with date related functions, I don't know if this issues applies on other functions.

Thanks!

Answer 1

Use a specially designed function — FromOADate:

double example = 44424.613976967594;
DateTime date = DateTime.FromOADate( example );

Answer 2

Dates in Excel are from 1899

Test :

                var nResult = EvaluteFormula("TODAY()");
                DateTime dt = new DateTime(1899, 12, 30);
                DateTime dtToday = dt.AddDays(nResult);

and I get :

dtToday = {16/08/2021 00:00:00}

Answer 3

you can force the date formatting by adding a text command that specifies the required formatting try using

=TEXT(TODAY(),"DDD DD MMM YYYY")