CODEWITHSUNDEEP
c#exceldatetime
littlecodefarmer758
littlecodefarmer758

Reputation: 966

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

I have a c# program which needs to create an excel object, and do some operations, here are parts 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 make the output become "2012/9/20" again?

I have tried some codes, but fail:

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

Upvotes: 2

Views: 14699

Answers (4)

ígor
ígor

Reputation: 1164

You can try this:

First set the format in the cell:

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

Then set value as DateTime:

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

And to get value use the following:

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

Upvotes: 14

Shumii
Shumii

Reputation: 4581

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

Once you do that you will get a proper double value for your time (which can be converted via the OADate function). What your getting now is nonsense.

Upvotes: 0

akton
akton

Reputation: 14386

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";.

Upvotes: 2

Artless
Artless

Reputation: 4568

By typing is `=2012/9/20" you're telling Excel that this is a formula. 2012 divided by 9, divided by 20 equal 11.17777777778.

Try storing the date without the = sign.

Upvotes: 3

Related Questions