How to convert a double value to a DateTime in c#?

Question

I have the value 40880.051388 and am storing it as a double, if I open Excel and paste in a cell and apply the following custom format "m/d/yyyy h:mm" to that cell, I get "12/3/2011 1:14"

How can I do this parsing/Conversion in C#? I don't know if the value is milliseconds from a certain checkpoint, like epoch time, or if the value is in some specific prepared format, but how does excel come up with this particular value? Can it be done in C#?

I've tried working with TimeSpan, DateTime, and other like things in Visual Studio but am not getting anywhere.

Maybe help you: http://stackoverflow.com/questions/8829171/conversion-of-double-to-datetime-always-fails — Felipe Oriani, Dec 17 '12 at 18:14
if you don't know what the number actually represents how to you know that Excel's choice of how to format it is correct? You should know what date that number actually corresponds to, and determine what the number logically represents, before considering how to program it. — Servy, Dec 17 '12 at 18:20
possible duplicate of [How to convert "double" to "datetime" between Excel and c#](http://stackoverflow.com/questions/12561042/how-to-convert-double-to-datetime-between-excel-and-c-sharp) — Andrew Whitaker, Dec 17 '12 at 19:24

score 35 · Accepted Answer · answered Dec 17 '12 at 18:15

35

Looks like you're using the old OLE Automation date. Use

DateTime.FromOADate(myDouble)

answered Dec 17 '12 at 18:15

Jaime Torres

10,365
1
48
56

2

Here's the Joel (not me) version of some of the information: http://www.joelonsoftware.com/items/2008/02/19.html – Joel Rondeau Dec 17 '12 at 18:30
@JoelRondeau I just discovered that `DateTime.FromOADate` gives a precision of only 1 millisecond. You can get a much higher precision if you care. See [my new answer](http://stackoverflow.com/a/13922172/1336654) in an old thread. – Jeppe Stig Nielsen Dec 17 '12 at 21:10

score 4 · Answer 2 · answered Dec 17 '12 at 18:14

4

Try something like this:-

double d = 40880.051388 ;
DateTime dt = DateTime.FromOADate(d);

answered Dec 17 '12 at 18:14

Rahul Tripathi

168,305
31
280
331

score 3 · Answer 3 · edited May 23 '17 at 10:31

3

Try using var dateTime = DateTime.FromOADate(40880.051388);.

If you need to format it to a string, use dateTime.ToString("M/d/yyyy H:mm", CultureInfo.InvariantCulture) for that. That will give you 24-hour string (change H to h for a 12-hour system).

If you need greater precision (by a factor 1000 or more) than offered by FromOADate, see my answer in another thread.

edited May 23 '17 at 10:31

Community

1
1

answered Dec 17 '12 at 18:15

Jeppe Stig Nielsen

60,409
11
110
181

Cory Nelson · Answer 4 · 2012-12-17T18:25:56.947

2

The value is an offset in days from December 30th, 1899. So you want:

new DateTime(1899, 12, 30).AddDays(40880.051388)

edited Dec 17 '12 at 18:25

answered Dec 17 '12 at 18:14

Cory Nelson

29,236
5
72
110

You're right, I forgot they use 12/30/1899 as a start point. Weird Excel people. Fixed. – Cory Nelson Dec 17 '12 at 18:25
1

@EricLippert Very true. But also it loses precision because the argument is rounded/truncated to nearest millisecond. Do you know why the framework always throws away precision like that? It makes no sense. I just wrote [a long answer in another thread](http://stackoverflow.com/a/13922172/1336654) that I hope you want to read. – Jeppe Stig Nielsen Dec 17 '12 at 21:37

score 0 · Answer 5 · answered Apr 06 '16 at 13:49

The following simple code will work

DateTime.FromOADate(myDouble)

However if performance is critical, it may not run fast enough. This operation is very processor intensive because the range of dates for the OLE Automation Date format begins on 30 December 1899 whereas DateTime begins on January 1, 0001, in the Gregorian calendar.

FromOADate calls a DoubleDateToTicks function using myDouble as the only argument. This returns the number of ticks, and this value is used to create a new DateTime with unspecified DateTimeKind.

The vast bulk of this work is done by the DoubleDateToTicks function in mscorlib. This includes code to throw an ArgumentException when the value of the double is NaN, and there are numerous ways in which it can be performance optimized depending on your exact needs.

How to convert a double value to a DateTime in c#?

5 Answers5

Linked