|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Convertin Double value to DateTimeHi
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 Hello Barry,
I just replied to you on another thread in this newsgroup. Please don't post your questions more than once. Oliver Sturm "Barry" <some***@somewheer.com> wrote in message The Excel value is in days since Excel's base time, which is by default Jan news:uqo2F5cHHHA.420@TK2MSFTNGP02.phx.gbl... > 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) 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 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 Show quote "Peter Duniho" <NpOeStPe***@NnOwSlPiAnMk.com> wrote in message news:12nsvl54gnq46f8@corp.supernews.com... > "Barry" <some***@somewheer.com> wrote in message > news:uqo2F5cHHHA.420@TK2MSFTNGP02.phx.gbl... >> 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" <some***@somewheer.com> wrote in message I'm glad I could help. What issues are you running into "when the data has news:%23jqhUXrHHHA.1912@TK2MSFTNGP03.phx.gbl... > 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. 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 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 Show quote "Peter Duniho" <NpOeStPe***@NnOwSlPiAnMk.com> wrote in message news:12o0i9p4l3jm070@corp.supernews.com... > "Barry" <some***@somewheer.com> wrote in message > news:%23jqhUXrHHHA.1912@TK2MSFTNGP03.phx.gbl... >> 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" <some***@somewheer.com> wrote in message Sorry...I made the poor assumption that the Seconds property was read/write.news:eg7S7Q2HHHA.3952@TK2MSFTNGP02.phx.gbl... > [...] > tsExcel.Seconds += (int)flTime * 24 * 60 * 60; > > shows an error since tsExcel.Seconds is read only 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" <NpOeStPe***@NnOwSlPiAnMk.com> wrote in message And of course, "txExcel" should be "tsExcel".news:12o358ueb0jumb2@corp.supernews.com... > Try this: > > txExcel = tsExcel + TimeSpan.FromSeconds((int)flTime * 24 * 60 * 60); Not sure I ought to be posting while under the influence of muscle relaxants. Seems like my brain is being relaxed too. :) Barry wrote:
> Hi Morning Barry> > 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 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 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. Show quote "SMJT" <shanemjtowns***@hotmail.com> wrote in message news:1166091359.479380.239590@79g2000cws.googlegroups.com... > > Barry wrote: > >> 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" <some***@somewheer.com> wrote in message You should make sure that you get the results you expect from FromOADate. news:uN1SiM4HHHA.1912@TK2MSFTNGP03.phx.gbl... > "SMJT" <shanemjtowns***@hotmail.com> wrote in message > news:1166091359.479380.239590@79g2000cws.googlegroups.com... >>> I think you may be able to use this, instead of all that string >> conversion stuff. >> >> DateTime.FromOADate(dblTimeVar); > > Just what the doctor ordered. 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  |
|||||||||||||||||||||||
Other interesting topics