How to convert "double" to "datetime" between Excel and C #

Question

I have a C # program that needs to create an excel object and perform some operations, here is part of my code:

// c# code: workSheet.Cells[1, 1] = "=2012/9/20"; //asign "2012/9/20" to cell[1,1] in Excel double d = workSheet.Cells[1, 1].value(); // by default, Excel will return 11.17 Debug.Print(d.ToString()); //c#: d = 11.1777777777778 

So, how to conclude again "2012/9/20"?

I tried some codes but did not execute:

 DateTime str = DateTime.FromOADate(d); //c#: str = 1/10/1900 4:16:00 AM DateTime str = new DateTime((long)d); //c#: str = 1/1/0001 12:00:00 AM 

Answer 1

You can try the following:

First set the format in the cell:

  ws.Cells[1,1].Style.Numberformat.Format = "yyyy/MM/dd"; 

Then set the value as DateTime:

 workSheet.Cells[1, 1] =new DateTime(2012,9,20); 

And to get the value use the following:

 double d = double.Parse(workSheet.Cells[1, 1].value()); DateTime conv = DateTime.FromOADate(d); 

Answer 2

By typing "= 2012/9/20", you tell Excel that it is a formula. 2012 divided by 9 divided by 20 is 11.1777777777778.

Try to save the date without the = sign.

Answer 3

If you want to store a literal or preformatted value in an Excel cell, precede the value with a single quote ' . For example, workSheet.Cells[1, 1] = "'2012/9/20"; .

Answer 4

Dude, your excel value is not a date. Put an equal sign at the beginning.

After that, you will get the correct double value for your time (which can be converted using the OADate function). What you are getting now is nonsense.