Resolving The Problem
The IBM i Access for Windows data transfer Add-in for Excel is capable of converting a large number of date and time formats that Excel can use. Not all of the available custom and standard time and date formats will work. The lists below describe the time and date formats that will work and some that will not work at the time of this writing.
| Custom Time Formats |
| Type (as listed on the Custom category) | Format string (as handled internally by Excel and Data Transfer) |
| h:mm AM/PM | h:mm AM/PM |
| h:mm:ss AM/PM | h:mm:ss AM/PM |
| h:mm | h:mm |
| h:mm:ss | h:mm:ss |
| h]:mm:ss | [h]:mm:ss |
| mm:ss.0 | mm:ss.0 |
| mm:ss | mm:ss |
| hh.mm.ss | hh.mm.ss |
| [$-409]h:mm:ss AM/PM | [$-409]h:mm:ss AM/PM |
| hh:mm:ss | hh:mm:ss |
| Standard Time Formats |
| Type (as listed on the Time category) | Format string (as handled internally by Excel and Data Transfer) |
| *1:01:01 AM | [$-F400]h:mm:ss AM/PM |
| 13:30 | h:mm;@ |
| 1:30 PM | [$-409]h:mm AM/PM;@ |
| 13:30:55 | h:mm:ss;@ |
| 1:30:55 PM | [$-409]h:mm:ss AM/PM;@ |
| 30:55.2 | mm:ss.0;@ |
| 37:30:55 | [h]:mm:ss;@ |
| * Except for this item, applied format does not switch time order with the operating system |
| Custom Date Formats |
| Type (as listed on the Date category) | Format string (as handled internally by Excel and Data Transfer) |
| m/d/yy | mm/dd/yy |
| d-mmm-yy | d-mmm-yy |
| d-mmm | d-mmm |
| mmm-yy | mmm-yy |
| m/d/yy h:mm | m/d/yy h:mm |
| yyyy-mm-dd | yyyy-mm-dd |
| mm/dd/yy | mm/dd/yy |
| Standard Date Formats |
| Type (as listed on the Date category) | Format string (as handled internally by Excel and Data Transfer) |
| *3/14/2001 | m/d/yyyy |
| *Wednesday, March 14, 2001 | [$-F800]dddd, mmmm dd, yyyy |
| 3/14 | m/d;@ |
| 3/14/01 | m/d/yy;@ |
| 03/14/01 | mm/dd/yy;@ |
| 14-Mar | [$-409]d-mmm;@ |
| 14-Mar-01 | [$-409]d-mmm-yy;@ |
| 14-Mar-01 | [$-409]dd-mmm-yy;@ |
| Mar-01 | [$-409]mmm-yy;@ |
| March-01 | [$-409]mmmm-yy;@ |
| March 14, 2001 | [$-409]mmmm d, yyyy;@ |
| 3/14/01 1:30 PM | [$-409]m/d/yy h:mm AM/PM;@ |
| 3/14/01 13:30 | m/d/yy h:mm;@ |
| M | [$-409]mmmmm;@ |
| M-01 | [$-409]mmmmm-yy;@ |
| 13/14/2001 | m/d/yyyy;@ |
| 14-Mar-2001 | [$-409]d-mmm-yyyy;@ |
| * Except for these items, applied formats do not switch date order with the operating system |
The items in bold are supported with service pack 3 (SI32504) of V6R1 System i Access for Windows. The items that are underlined are not presently supported.