Convertin Double value to DateTime

[Barry]

Hi

In a excel sheet processed through C#, i get a Date (11/6/2006) as 39027,
how do i convert this value to a DateTime (maybe as a string)

DateTime dt = Convert.ToDateTime(39027); // shows error

TIA
Barry

 

[Oliver Sturm]

Hello Barry,

I just replied to you on another thread in this newsgroup. Please don't
post your questions more than once.


Oliver Sturm

 

[Peter Duniho]

Hi

In a excel sheet processed through C#, i get a Date (11/6/2006) as 39027,
how do i convert this value to a DateTime (maybe as a string)

The Excel value is in days since Excel's base time, which is by default Jan
1, 1900 under Windows. The default for Mac Excel is Jan 2, 1904. This date
can be changed using the Tools/Options... menu item.

Because the date storage format in Excel is changeable, the most reliable
method to solve your problem is probably to convert the date to text in
Excel, and process the text in .NET using the DateTime.Parse or ParseExact
method. For example:

* In Excel, create a cell that references your data cell using the
formula =TEXT(A1, "mm/dd/yyyy") where you've replaced "A1" with the actual
cell containing your serial date. Feel free to use a different number
format string as needed (and of course, use the matching pattern in the .NET
parse method).

* In your .NET program:

DateTime dtResult = DateTime.ParseExact(strExcel, "MM/dd/yyyy",
null);

The assumption here is that "strExcel" contains the string value obtained
from Excel after converting the date to a string in Excel.


If you are absolutely sure that the base time will always be the same, then
in .NET you could set a DateTime structure to the base time you're using,
set a TimeSpan structure to contain the number obtained from Excel as Days
(minus one, because Excel's dates are one-based, not zero-based), and then
add that to the base time DateTime structure. If you want to preserve
fractional dates (that is, a specific time within a date), you'll have to
convert the fractional part to hours, minutes, seconds, etc. as desired.
IMHO, the easiest way to do that would be to convert the fractional part to
the smallest division you're interested in, and add that to the DateTime
structure. For example:

DateTime dtResult, dtBase = new DateTime(1900, 1, 1);
TimeSpan tsExcel = new TimeSpan((int)flExcelDays - 1, 0, 0, 0);
float flTime = flExcelDays - (int)flExcelDays;

// convert fraction of a day into seconds
tsExcel.Seconds += flTime * 24 * 60 * 60;

dtResult = dtBase + tsExcel;

Assumptions here are that flExcelDays contains the floating point value of
the serial date from Excel, and that you will use the resulting date in the
"dtResult" variable.

Hope that helps. I apologize in advance for any errors...I'm up way too
late to be posting code.

Pete

 

[Barry]

Hi Pete

Thanks for your most appropriate reply.

My problem is that i take data from a excel sheet in C# and transfer the
data to a database, i cannot change the values as suggested (string
conversion), neverthless you 4-5 lines of code were sufficent to solve the
problem temporarily.

However when the data has a time component ( like 39027.091092219 etc) then
there are some issues needing to be looked.

In anycase if there was a Rating system on this site i would have suerly
given you 10 - out - 10 for the simple reason, that you wrote such a lengthy
reply, very care to do that.


Barry

 

[Peter Duniho]

Hi Pete

Thanks for your most appropriate reply.

My problem is that i take data from a excel sheet in C# and transfer the
data to a database, i cannot change the values as suggested (string
conversion), neverthless you 4-5 lines of code were sufficent to solve the
problem temporarily.

However when the data has a time component ( like 39027.091092219 etc)
then there are some issues needing to be looked.

I'm glad I could help. What issues are you running into "when the data has
a time component"? The code I posted is supposed to handle that fine, but I
suppose it could have an error. If you can describe exactly what goes wrong
in that situation, I might be able to fix it.

Pete

 

[Barry]

Hi Pete

can you please try to convert "39081.418668981481" (returned by C# for date
12/30/2006 10:02:53 AM from a excel spreadsheet) to a DateTime object using
the lines of code you gave in your original posting

this line

tsExcel.Seconds += (int)flTime * 24 * 60 * 60;

shows an error since tsExcel.Seconds is read only

i suppose you would convert "39081.???" to double

TIA
Barry

 

[SMJT]

Hi

In a excel sheet processed through C#, i get a Date (11/6/2006) as 39027,
how do i convert this value to a DateTime (maybe as a string)

DateTime dt = Convert.ToDateTime(39027); // shows error

TIA
Barry

Morning Barry

I think you may be able to use this, instead of all that string
conversion stuff.

DateTime.FromOADate(dblTimeVar);

where dblTimeVar is a double such 39065.42702 or whatever.

Regards

Shane

 

[Barry]

Just what the doctor ordered.

I generally don't post thank you message, but sometimes am forced to do so,

Thanks to you and Pete.

 

[Peter Duniho]

[...]
tsExcel.Seconds += (int)flTime * 24 * 60 * 60;

shows an error since tsExcel.Seconds is read only

Sorry...I made the poor assumption that the Seconds property was read/write.

Try this:

txExcel = tsExcel + TimeSpan.FromSeconds((int)flTime * 24 * 60 * 60);

i suppose you would convert "39081.???" to double

For your purposes, either float or double is probably fine, but yes, if you
don't use double it will get converted before being passed to FromSeconds
anyway.

Pete

 

[Peter Duniho]

Just what the doctor ordered.

You should make sure that you get the results you expect from FromOADate.
In particular, it uses a different base time than Excel (Dec 30, 1899
instead of Dec 31, 1899...Excel is actually Jan 1, 1900 but since it's
1-based, it might as well be Dec 31, 1899).

Also, FromOADate apparently has a strange interpretation of the integer and
fractional portion. That is, it removes the integer portion entirely before
considering the fractional portion, always adding the fractional portion
even if the number itself is negative. This probably won't come up, but it
means that (as the documentation says) -0.5 turns out to be the same date
and time as 0.5.

If one believes the documentation, using FromOADate you will wind up with an
off-by-one error on your dates.

Pete

 

[Peter Duniho]

Try this:

txExcel = tsExcel + TimeSpan.FromSeconds((int)flTime * 24 * 60 * 60);

And of course, "txExcel" should be "tsExcel".

Not sure I ought to be posting while under the influence of muscle
relaxants. Seems like my brain is being relaxed too.