No announcement yet.

Download to Excel Error

  • Filter
  • Time
  • Show
Clear All
new posts

  • Download to Excel Error

    I am downloading what is on my grid to excel and the dates in the excel are always yesterday's date. I thought at first it was me, then I hard coded the date I wanted and it brought in the correct data. But the dates are minus one day. ie. The dates in the actual records are 2016-01-14 and I'm seeing 2016-01-13. Sorry for all the downloads.
    DownloadExcelError2.jpg DownloadExcelError3.jpg
    Attached Files
    Last edited by JosephHarriman; 01-15-2016, 10:24 AM.

  • #2
    Very interesting -- I'm able to replicate this myself. Definitely looks like some sort of bug in the creation of the .xlsx date column. Will get back to you.


    • #3
      Okay, it appears this problem with your spreadsheet dates being offset by a day stems from the way dates are stored in .xlsx files. At some point in the early development history of spreadsheet programs, it was decided that calendar dates would be stored as an integer reflecting the number of days elapsed since Jan 1, 1900, with 1900-01-01=day 1, 1900-01-02=day 2, etc. But apparently someone on the development team assumed the year 1900 was a leap year (it wasn't), and thus the nonexistent date of 1900-02-29 was included in the calendar as day 60. This is sometimes referred to as the Leap Year Bug. The fact that Feb 29 did not really exist in 1900 is why you're seeing the subsequent dates in your Valence spreadsheet downloads being offset by one day.

      So the fix for the date problem in the Valence .xlsx download routine is to simply treat the root date of 1900-01-01 as "2" (instead of "1"). We'll include this adjustment on the next maintenance build. In the meantime, you should be able to patch the problem on your current Valence 4.2 instance by doing the following:
      • Go into the QRPGLESRC source for VVOUT and add a +1 to the end of line 5337.00, so it becomes this:
      • Save the source change, then compile the VVOUT module with this command:
      • Update the Valence service program with this command:
      • Restart your Valence instance. With VALENCE42 in your lib list, you can just type VVSTART at a command line.
      Last edited by robert.swanson; 01-19-2016, 10:51 AM. Reason: incorrect compile command


      • #4
        Thanks Robert. I've changed VVOUT but it will not compile. I've left a voice mail on the support line.


        • #5
          D'oh! Sorry about that, I gave you the wrong compile command for VVOUT. It should be CRTSQLRPGI, not CRTRPGMOD. I've updated the post above.

          I should mention there is a related fix involving timestamp columns that will be included in the next maintenance release.