2

How to convert in modern C++ (C++11/14/17) a double into a date time using the date.h library, when the double has been generated while exporting an Excel worksheet as a CSV file?

For instance, the datetime appearing in Excel:

21/08/2017 11:54

has been converted by Excel into the CSV file as the double:

42968.4958333333

Thanks.

EDIT on 11/07/2019: This questions is about the use of the date.h library. The other questions pointed out as "possible duplicates" does not require the use of this library (see also the comment below by the author of the date.h library)

stegua
  • 211
  • 5
  • 10
  • 2
    Depends on how Excel decides to convert the data to double, it would be a lot easier to help you if you could provide it. – Borgleader Oct 01 '17 at 20:16
  • Thanks for your comment. However, the number given in the example is the result of the Excel conversion. I have no idea how excel convert this number, otherwise I would know the answer to my question. – stegua Oct 01 '17 at 20:19
  • @Borgleader I just edit my question to try to clarify it. – stegua Oct 01 '17 at 20:23
  • 2
    https://stackoverflow.com/a/5023663/214671 – Matteo Italia Oct 01 '17 at 20:27
  • [Google helps you find the documentation](http://www.google.com/search?btnI=1&q=DATEVALUE+function) – Dúthomhas Oct 01 '17 at 20:31
  • [Bonus reading about the bizarre Excel 31/12/1899 epoch](https://www.joelonsoftware.com/2006/06/16/my-first-billg-review/) – Matteo Italia Oct 01 '17 at 20:34
  • 2
    If you use MFC/ATL, there is the very convenient [`COleDateTime` class](https://msdn.microsoft.com/en-us/library/38wh24td.aspx) which has a constructor that takes a `double` in this format. – alain Oct 01 '17 at 21:10
  • 1
    https://pastebin.com/XCmUzfuM I think.. `std::chrono` is still a bit uncivilized compared to `DateTime` classes in other languages like Java and C# and so on.. – Brandon Oct 01 '17 at 21:32
  • 2
    This question could not possibly be a duplicate of [date time conversion](https://stackoverflow.com/questions/5022864/date-time-conversion) because this question asks how to use the [date.h](https://github.com/HowardHinnant/date) library. This library did not even exist when the other question was asked. Nor has the other question been updated to ask about this newer library. – Howard Hinnant Jul 11 '19 at 13:49

1 Answers1

5

Using date.h, it could look like this:

#include "date/date.h"
#include <iostream>

std::chrono::system_clock::time_point
to_chrono_time_point(double d)
{
    using namespace std::chrono;
    using namespace date;
    using ddays = duration<double, days::period>;
    return sys_days{December/30/1899} + round<system_clock::duration>(ddays{d});
}

int
main()
{
    using date::operator<<;
    std::cout << to_chrono_time_point(42968.495833333333333333333) << '\n';
}

which outputs:

2017-08-21 11:54:00.000000

This definition assumes that your mapping from 42968.4958333333 to 21/08/2017 11:54 is correct. I see in one other place that the epoch is supposed to be 1899-12-31, not 1899-12-30. In any event, once the correct epoch is found, this is how one would perform the computation.

Ah, https://en.wikipedia.org/wiki/Leap_year_bug explains the off-by-one error. The writers of Excel purposely considered 1900 a leap year for the purpose of backwards compatibility with Lotus 1-2-3.

This output was generated on macOS where system_clock::duration is microseconds. The output will be slightly different on other platforms where system_clock::duration has other units.

Aside: At this range, the precision of an IEEE 64 bit double is coarser than nanoseconds but finer than microseconds (on the order of half of microsecond).

Howard Hinnant
  • 206,506
  • 52
  • 449
  • 577
  • 2
    Howard: thanks again for your answer. I really appreciate the support to your date.h library. – stegua Jul 11 '19 at 14:07