Excel date conversion in RPG

[gregwga50]

We have an Iseries file that was created by using Client Access file transfer to transfer an Excel spreadsheet to the Iseries. The Excel file has some columns that are of format type DATE but file transfer converted them to a numeric decimal field. I'm sure whoever executed the file transfer set it up wrong. Example 3/11/2017 in Excel became 42805 in the file that was created. I would prefer not to have to download the file again and have file transfer handle the dates differently. Does anyone know of a way to convert this field back to a date in an RPG program, i.e convert 42805 back to 3/11/2017. I was hoping there was a %BIF, API, C-function or homegrown RPG routine that somebody wrote that would do this.

[tomliotta]

I don't know enough about Excel dates to have a reliable answer. However, if I run this in STRSQL:

select (date('1899-12-31') + 42805 days) from sysibm.sysdummy1

The result is:

Date expression
  2017-03-12

Some variation of leap days between Excel and DB2 for i might account for any difference. Try similar RPG functions and try a good sequence of many dates if you can't wait for a better answer. Maybe you have a narrow enough range of dates that you can just make tiny procedure that can be corrected later or that will work for as long as the application survives..

[Scott Klement]

Yes, Excel keeps track of dates as a number of days since 1900, with the caveat that Excel incorrectly believes 1900 to have been a leap year, so any dates after Feb 28, 1900 are one day off.

This is the code that I use (from my HSSFR4 service program):

      *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
      *  ss_xls2date():
      *    service program utility to convert an Excel date to
      *    an RPG date field
      *
      *    peXls = Number used as a date in Excel
      *
      *  returns the RPG date
      *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
     P ss_xls2date     B                   EXPORT
     D ss_xls2date     PI              D
     D   peXls                             like(jdouble) value

     D wwStrDate       s               d   inz(d'1900-01-01')
     D wwDate          s               d

      **
      ** See ss_date2xls for comments on how the Excel date format works
      **
      /free

         wwDate = wwStrDate + %days(%int(peXls) - 1);

         // Excel incorrectly thinks that 1900-02-29 is
         //  a valid date.

         if (wwDate > d'1900-02-28');
              wwDate = wwDate - %days(1);
         endif;

         return wwDate;
      /end-free
     P                 E

Note: jdouble is defined as "8F" (8-byte floating point)

[gregwga50]

Yes, Excel keeps track of dates as a number of days since 1900, with the caveat that Excel incorrectly believes 1900 to have been a leap year, so any dates after Feb 28, 1900 are one day off.

Thanks Scott, this will help me a lot. that way, I don't have to have to try and download the file again as CA File Transfer can be somewhat temperamental when dates are invloved

[gregwga50]

Yes, Excel keeps track of dates as a number of days since 1900, with the caveat that Excel incorrectly believes 1900 to have been a leap year, so any dates after Feb 28, 1900 are one day off.

This is the code that I use (from my HSSFR4 service program):

*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* ss_xls2date():
* service program utility to convert an Excel date to
* an RPG date field
*
* peXls = Number used as a date in Excel
*
* returns the RPG date
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
P ss_xls2date B EXPORT
D ss_xls2date PI D
D peXls like(jdouble) value

D wwStrDate s d inz(d'1900-01-01')
D wwDate s d

**
** See ss_date2xls for comments on how the Excel date format works
**
/free

wwDate = wwStrDate + %days(%int(peXls) - 1);

// Excel incorrectly thinks that 1900-02-29 is
// a valid date.

if (wwDate > d'1900-02-28');
wwDate = wwDate - %days(1);
endif;

return wwDate;
/end-free
P E

Note: jdouble is defined as "8F" (8-byte floating point)

I am using this in several of my programs and it works quite well for what I need to do. I was just wondering if it is necessary for the incoming parameter to be FLOAT(8).

Thanks again

[Scott Klement]

Internally, Excel stores all numbers as 64-bit floating point (aka "double", or float 8). That is why I used it here... But, you have the source code, so you can change it nny way you see fit...

[arrow483]

FWIW: Excel is right. 1900 was NOT a leap year.

• The year can be evenly divided by 4;
• If the year can be evenly divided by 100, it is NOT a leap year, unless;
• The year is also evenly divisible by 400. Then it is a leap year.

[tomliotta]

FWIW: Excel is right. 1900 was NOT a leap year.

Excel is wrong because 1900 was NOT a leap year.

That is, Excel adds an erroneous extra day after 28-Feb-1900 that needs to subtracted back off when converted to a DB2 for i DATE. Excel treats 1900 as a leap year and adds a day that didn't exist, so the subtraction is needed for correction.