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.

Distrain

Maybe help you: #8829671 – Wimberly

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. – Circumgyration

possible duplicate of How to convert "double" to "datetime" between Excel and c# – Bedad

Answer 1

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

DateTime.FromOADate(myDouble)

Ralph

Here's the Joel (not me) version of some of the information: joelonsoftware.com/items/2008/02/19.html – Kinaesthesia

@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 in an old thread. – Versatile

Answer 2

Try something like this:-

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

Incurvate

Answer 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.

Versatile

Answer 4

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

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

Wifehood

You're right, I forgot they use 12/30/1899 as a start point. Weird Excel people. Fixed. – Wifehood

@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 that I hope you want to read. – Versatile

Answer 5

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.

Digger