OADate Origin在.NET和Excel中有所不同 [英] OADate Origin is Different in .NET and Excel
问题描述
如果您使用OADates将时间用于实际线路以用于科学和工程目的，您可能会对以下内容感到疑惑：Microsoft为Visual Basic .NET和Excel选择了不同的时间源。
Visual Basic的原点是12/30/1899 00 ：00。
Excel的原点是12/31/1899 00 ：00。
例如
Dim x as Double
x =" 1/1/1900 06：00" .ToOADate
MsgBox（x）
显示2.25
在Excel中的VBA中也是如此。但是如果你去同一个Excel中的电子表格，并将相邻的单元格格式化为日期和数字，如果你输入
1/1 / 1900 06:00
在第一个
并复制该单元格，并粘贴特殊的"值"进入格式化为数字的第二个单元格，你得到
1.25
如果在Excel中在VBA中编写相同的3行程序，并添加
Sheet1.Cells（ 1,1）.Value = x
您在单元格A1中得到以下内容
< p class = MsoNoSpacing style ="margin：0in 0in 0pt">
1/2/1900 06 ：00
将在Excel表格中显示的日期将与程序中的日期相隔一天。
负OADates完全不起作用：
http://forums.microsoft.com /MSDN/ShowPost.aspx?PostID=2770285&SiteID=1
科学家和工程师需要能够映射约会到实线，因为简单的添加不足以解决复杂的问题。通常，您需要指定
y = f（t），
< font face = Calibri size = 3>其中函数f是时间t的非线性函数。
例如，它可能是一个多项式拟合，
y = a + b * t + c * t ** 2
它可能是傅里叶级数，
y = a + b * sin（t）+ c * sin（2 * t）+ ...
包之间的转移或起源，以及事实上负面的OADates左侧是积分部分而右侧是分数部分，这大大降低了这些软件包对于时间起关键作用的任何科学或工程应用的有用性。
Excel和Visual Basic之间的差异仅适用于1900年3月1日之前的日期。 如果您进行上述相同的实验并使用"3/1/1900"，作为您的考试日期，Excel和VB都将返回61.25。 早期
日期的差异是由于Excel错误地认为1900是闰年这一事实，这在其他实现中已得到修复。 （在Excel中，2/29/1900的OADate为60，而VB认为60为2/28。）
选择VB的不同起源日期，以便Excel中的闰年错误可以在其他实现中修复，同时仍保持当前日期的相同值。
If you use OADates to put time on the real line for scientific and engineering purposes, you might wonder about the following: Microsoft chose a different time origin for Visual Basic .NET and for Excel.
The origin for Visual Basic is 12/30/1899 00:00.
The origin for Excel is 12/31/1899 00:00.
For example
Dim x as Double
x = "1/1/1900 06:00".ToOADate
MsgBox(x)
shows 2.25
The same is true in VBA within Excel. But if you go to the spreadsheet in that very same Excel, and format adjacent cells as date and number, if you type
1/1/1900 06:00
in the first
and copy that cell, and paste special "value" into the second cell formatted as a number, you get
1.25
If you write the same 3-line program in the VBA on within Excel, and add
Sheet1.Cells(1,1).Value = x
you get the following in cell A1
1/2/1900 06:00
The dates that will show in Excel sheets will be off one day from those in your program.
And negative OADates don't work at all:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2770285&SiteID=1
Scientists and engineers need to be able to map dates to the real line, because simple addition is not adequate for complex problems. In general, you need to specify
y = f(t),
where function f is a nonlinear and involved function of time t.
For example, it might be a polynomial fit,
y = a + b*t + c*t**2
It might be a Fourier series,
y = a + b*sin(t) + c*sin(2*t) + ...
The shift or origin between packages, and the fact that negative OADates go to the left for the integral part and to the right for the fractional part, detract considerably from the usefulness of these packages for any science or engineering applications in which time plays a critical role.
The discrepancy between Excel and Visual Basic only exists for dates before March 1st, 1900. If you do the same experiment above and use "3/1/1900" as your test date, both Excel and VB will return 61.25. The discrepancy for earlier dates is due to the fact that Excel incorrectly considers 1900 to be a leap year, which was fixed in other implementations. (In Excel, 2/29/1900 has an OADate of 60, while VB considers 60 to be 2/28.)
The different origin date for VB was chosen so that the Leap Year bug in Excel could be fixed in other implementations whiles still keeping the same value for current dates.
这篇关于OADate Origin在.NET和Excel中有所不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
