Troubleshooting
Problem
NOTE: This document pertains to products that have reached their end of support date and are no longer provided by, nor supported by IBM.
The System 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.
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.
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.
| 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.
[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z000000cwMLAAY","label":"Data Access-\u003EData Transfer"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"6.1.0;7.1.0"}]
Historical Number
496191501
Was this topic helpful?
Document Information
More support for:
IBM i
Component:
Data Access->Data Transfer
Software version:
6.1.0, 7.1.0
Operating system(s):
IBM i
Document number:
635433
Modified date:
13 November 2024
UID
nas8N1013524
Manage My Notification Subscriptions