Algorithm for converting Serial Date (Excel) to Year-Month-Day in C++

user_2287458 0
Viewed 828

This post here provides a very neat & pure C++ algorithm for converting a serial date (Excel) to its explicit year-month-day representation (and back). Let me paste a compressed version for convenience:

void ExcelSerialDateToDMY(int nSerialDate, int& nDay, int& nMonth, int& nYear)
{
    // 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;
}

int DMYToExcelSerialDate(int nDay, int nMonth, int nYear)
{
    // DMY to Modified Julian calculated with an extra subtraction of 2415019.
    return 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;
}

For example

 2019-06-22 <--> 43638
 2000-01-28 <--> 36553
 1989-09-21 <--> 32772

The above post is from 2002, so I am wondering whether there are alternative implementations, which are better. By "better" I mean e.g. faster, shorter or less obscure. Or even algorithms, which perhaps provide a certain amount of pre-calculations (e.g. record 1 Jan serial date for a desired range of years, say 1900 to 2200, and then perform a fast look up).

c++17 date datetime parsing c++
1 Answers

The algorithms you show are very good. On my platform (clang++ -O3) they produce object code with no branches (pipeline stallers) and no accesses to far away memory (cache misses). As a pair, there is a range of validity from -4800-03-01 to millions of years in the future (plenty of range). Throughout this range they model the Gregorian calendar.

Here are some alternative algorithms that are very similar. One difference is that yours have an epoch of 1900-01-01 and the ones I'm presenting have an epoch of 1970-01-01. However it is very easy to adjust the epoch by the difference of these epochs (25569 days) as shown below:

constexpr
std::tuple<int, unsigned, unsigned>
civil_from_days(int z) noexcept
{
    static_assert(std::numeric_limits<unsigned>::digits >= 18,
             "This algorithm has not been ported to a 16 bit unsigned integer");
    static_assert(std::numeric_limits<int>::digits >= 20,
             "This algorithm has not been ported to a 16 bit signed integer");
    z += 719468 - 25569;
    const int era = (z >= 0 ? z : z - 146096) / 146097;
    const unsigned doe = static_cast<unsigned>(z - era * 146097);          // [0, 146096]
    const unsigned yoe = (doe - doe/1460 + doe/36524 - doe/146096) / 365;  // [0, 399]
    const int y = static_cast<int>(yoe) + era * 400;
    const unsigned doy = doe - (365*yoe + yoe/4 - yoe/100);                // [0, 365]
    const unsigned mp = (5*doy + 2)/153;                                   // [0, 11]
    const unsigned d = doy - (153*mp+2)/5 + 1;                             // [1, 31]
    const unsigned m = mp + (mp < 10 ? 3 : -9);                            // [1, 12]
    return std::tuple<int, unsigned, unsigned>(y + (m <= 2), m, d);
}

constexpr
int
days_from_civil(int y, unsigned m, unsigned d) noexcept
{
    static_assert(std::numeric_limits<unsigned>::digits >= 18,
             "This algorithm has not been ported to a 16 bit unsigned integer");
    static_assert(std::numeric_limits<int>::digits >= 20,
             "This algorithm has not been ported to a 16 bit signed integer");
    y -= m <= 2;
    const int era = (y >= 0 ? y : y-399) / 400;
    const unsigned yoe = static_cast<unsigned>(y - era * 400);      // [0, 399]
    const unsigned doy = (153*(m + (m > 2 ? -3 : 9)) + 2)/5 + d-1;  // [0, 365]
    const unsigned doe = yoe * 365 + yoe/4 - yoe/100 + doy;         // [0, 146096]
    return era * 146097 + static_cast<int>(doe) - (719468 - 25569);
}

These algorithms are valid for millions of years both forward and backwards (including prior to -4800-03-01). Though that extra range won't buy you much because the Gregorian calendar didn't even start until 1582-10-15.

I compiled both pairs of algorithms on macOS using clang++ -O3 -S and the set I have produces slightly smaller object code (about 10%). Though they are all so small, branch-less and cache-miss-free, trying to verify that benefit by measuring performance would be a challenging exercise.

I do not find the readability of either set superior to the other. However this pair of algorithms does come with an irritatingly exhaustive derivation for those who are curious how these algorithms work, and unit tests to ensure the algorithms are working over a range of +/-1 million years.

One could gain a very slight bit of performance in the above algorithms by limiting the range of validity to [2000-03-01, 2400-02-29] by setting const int era = 5 in both algorithms. I have not performance tested this option. I would expect such a gain to be in the noise level.

Or there might be some miniscule performance advantage by limiting the range from [0000-03-01, millions of years forward] by not accounting for negative values of era:

In civil_from_days:

const int era = z / 146097;

In days_from_civil:

const int era = y / 400;
user_5769110
Related