Excel 序列日期转换为日、月、年以及反之

引言

为了完成我一个小型项目，我需要将序列日期数字转换为日/月/年 (DMY) 格式，以及反之。这个序列数字来自于一个转换后的 Paradox 数据库中的日期字段，并且看起来与 Microsoft Excel 中的序列日期完全相同，因此才有了本文的名称。在 Google 上搜索后，我了解到 Excel 序列日期与儒略日期有关，并找到了一种快速的算法来将这些数字转换为 DMY 格式以及反之。

Excel 序列日期数字

那么什么是 Excel 序列日期数字？37477 是一个序列日期数字，代表 *自 1900 年 1 月 1 日以来的天数*。37477 实际上对应于 2002 年 8 月 9 日，即本文撰写日期。

当然，一旦你知道闰年的计算方法，计算自 1900 年 1 月 1 日以来的天数就并不难。然而，Microsoft Excel 的日期计算中包含一个错误：它将 1900 年 2 月 29 日视为有效日期，但 1900 年不是闰年！1900 年 2 月 29 日不是一个有效的日期！

根据故事，Microsoft 决定从 Lotus 123 中复制这个日期错误，当时 Lotus 123 是占据主导地位的电子表格应用程序。这使得 Excel 能够打开 Lotus 123 电子表格而不会出现日期问题。

(请注意，序列日期数字也可能包含表示时间的小数部分。它实际上是 24 小时的百分比，但这不在本文讨论范围内。)

Mac Excel 序列日期数字

Apple Mac 上的 Excel 使用略有不同的序列日期数字基数：*自 1904 年 1 月 1 日以来的天数*。这就是为什么 Excel 在其选项（计算选项卡）中具有“1904 年日期系统”复选框。我不会对此进行进一步的讨论。

将序列日期数字转换为 DMY

首先，功劳归功于功劳。我从 这里 获取了基本算法。它是关于使用整数计算将儒略日期转换为 DMY 的。我不会用理论来烦你（请按照上面的超链接），但原始儒略日期具有不同的基准日期。

通过添加一个特定的数字，该算法接近 Excel 序列日期。并且在处理 1900 年 2 月 29 日的问题后，我们就得到了一个 Excel 序列日期到日、月、年的计算方法！

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;
}

DMY 转换为 Excel 序列日期

根据日、月、年计算 Excel 序列日期。该函数假定日、月、年是有效的日期数字。

int DMYToExcelSerialDate(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 (nDay == 29 && nMonth == 02 && nYear==1900)
        return 60;

    // DMY to Modified Julian calculated with an extra subtraction of 2415019.
    long nSerialDate = 
            int(( 1461 * ( nYear + 4800 + int(( nMonth - 14 ) / 12) ) ) / 4) +
            int(( 367 * ( nMonth - 2 - 12 * ( ( nMonth - 14 ) / 12 ) ) ) / 12) -
            int(( 3 * ( int(( nYear + 4900 + int(( nMonth - 14 ) / 12) ) / 100) ) ) / 4) +
            nDay - 2415019 - 32075;

    if (nSerialDate < 60)
    {
        // Because of the 29-02-1900 bug, any serial date 
        // under 60 is one off... Compensate.
        nSerialDate--;
    }

    return (int)nSerialDate;
}