如何将 Excel 序列日期号转换为 .NET DateTime？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/727466/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I convert an Excel serial date number to a .NET DateTime?
提问by David Basarab
How do I convert from excel serial date to a .NET date time?
如何从 excel 序列日期转换为 .NET 日期时间？
For example 39938 is 05/05/2009.
例如 39938 是 05/05/2009。
回答by Narcís Calvet
I find it simpler using FromOADate method, for example:
我发现使用FromOADate 方法更简单，例如：
DateTime dt = DateTime.FromOADate(39938);
Using this code dtis "05/05/2009".
使用此代码dt是“05/05/2009”。
回答by Joel Coehoorn
Where 39938 is the number of days since 1/1/1900?
其中 39938 是自 1/1/1900 以来的天数？
In that case, use the framework library function DateTime.FromOADate().
This function encapsulatesall the specifics, and does bounds checking.
在这种情况下，请使用框架库函数DateTime.FromOADate()。
该函数封装了所有细节，并进行边界检查。
For its historical value, here is a possible implementation:
对于它的历史价值，这里是一个可能的实现：
(C#)
（C＃）
public static DateTime FromExcelSerialDate(int SerialDate)
{
if (SerialDate > 59) SerialDate -= 1; //Excel/Lotus 2/29/1900 bug
return new DateTime(1899, 12, 31).AddDays(SerialDate);
}
VB
VB
Public Shared Function FromExcelSerialDate(ByVal SerialDate As Integer) As DateTime
If SerialDate > 59 Then SerialDate -= 1 ' Excel/Lotus 2/29/1900 bug
Return New DateTime(1899, 12, 31).AddDays(SerialDate)
End Function
[Update]:
Hmm... A quick test of that shows it's actually two days off. Not sure where the difference is.
[更新]：
嗯...快速测试表明它实际上是两天休息。不知道区别在哪里。
Okay: problem fixed now. See the comments for details.
好的：问题现在解决了。详情见评论。
回答by Aistis Raudys
For 39938 do this: 39938 * 864000000000 + 599264352000000000
对于 39938，请执行以下操作：39938 * 864000000000 + 599264352000000000
864000000000 represents number of ticks in a day 599264352000000000 represents number of ticks from the year 0001 to the year 1900
864000000000 代表一天中的滴答数 599264352000000000 代表从 0001 年到 1900 年的滴答数
回答by Zymotik
void ExcelSerialDateToDMY(int nSerialDate, int &nDay,
int &nMonth, int &nYear)
{
// Excel/Lotus 123 have a bug with 29-02-1900. 1900 is not a
// leap year, but Excel/Lotus 123 think it is...
if (nSerialDate == 60)
{
nDay = 29;
nMonth = 2;
nYear = 1900;
return;
}
else if (nSerialDate < 60)
{
// Because of the 29-02-1900 bug, any serial date
// under 60 is one off... Compensate.
nSerialDate++;
}
// Modified Julian to DMY calculation with an addition of 2415019
int l = nSerialDate + 68569 + 2415019;
int n = int(( 4 * l ) / 146097);
l = l - int(( 146097 * n + 3 ) / 4);
int i = int(( 4000 * ( l + 1 ) ) / 1461001);
l = l - int(( 1461 * i ) / 4) + 31;
int j = int(( 80 * l ) / 2447);
nDay = l - int(( 2447 * j ) / 80);
l = int(j / 11);
nMonth = j + 2 - ( 12 * l );
nYear = 100 * ( n - 49 ) + i + l;
}
Cut and Paste of someone elses talents...
剪切和粘贴别人的才能...
