Excel Report library - making Excel formatted date/time

[ElectroLund]

I spent over an hour trying to find an algorithm or API function to convert from the many CVI date / time functions into Excel's format (what they call date "serial number"). See here for a description of the format.  This is necesary if you intend to use the ExRConst_dataDate type with the Excel Report writer functions.

I found that if you use CVI function GetCurrentDateTime and convert to days (value / 60 / 60 / 24), you'll get something like: 42576.418688912.  Plunk that into Excel and format for date, and you'll get... two days ago!  It's mysteriously off by exactly 2 days.

---

Going a different route, I found that if you simply format your date time into a string like this:

1/2/2016  1:47:37 PM

And send this as ExRConst_dataString, Excel is very forgiving and will treat these as date & time.  You can even sort the date in Excel correctly.  Note that this post helped a lot with proper string formatting.

Has anyone else had luck with ExRConst_dataDate? 

[MWighton]

I am also having this issue. Funny thing is, my code was running perfectly using GetCurrentDateTime and then about a week ago excel decided it wanted to be 2 days behind.

Any ideas how to fix this, without having to add 2 to the value in excel?

[ElectroLund]

No, sorry, I never did.  I just switched over to string type.

And then much later, I switched entirely from the Excel lib to the TDMS lib.  Not what you wanted to hear, but there you go.