Resolving The Problem

Note: This macro might not be necessary if the client is V5R3 iSeries Access or newer and the option is used to 'Convert Excel date and time cells to System i date or time'. This option can be found in the Data Transfer Properties.

Microsoft Excel stores dates in a numeric format in such a way that they are a long integer value rather than a date. This can present problems when a person tries to upload spreadsheet data using the IBM iSeries Access data transfer function. When uploading the data to an existing file, these dates must first be converted to a text or character data type. The upload wizards provided with data transfer or the Excel plug-in will allow the user to create a new file in the IBM System i products based on a scan of the spreadsheet. When the scan is performed, columns that have dates stored in Excel's native format will be detected as numeric types. Although the column type can be modified after the scan completes, it is not possible to switch a numeric type to a Date type. To switch the column to a date type, the scan must detect the column as a character type. The way to convert the cell value to character data is to insert an apostrophe ahead of the date data so that, for example, 1/1/2000 becomes '1/1/2000.

Note: The following instructions could vary depending on the version of Excel being used. Converting many cells in this manner is tedious and can even be error prone. As a service to our customers, we have provided the following directions for creating a macro in Excel to automate this process. Macros are often saved with the Excel spreadsheet, so the first step is to open the spreadsheet that needs to have the conversion performed. Next, click on the Tools menu and select Macro and then Macros. This will open the macro dialog box. Type 'a' or any other character in the text box labeled Macro name: and then click the Create button. This will open the Microsoft Visual Basic editor and it will have the shell of the macro defined for you. It will look like the following:

Sub a()

End Sub

Delete that text and type or paste in the following;

Sub ConvertDatesToStrings()
    Dim count As Integer
    count = 0
    For Each c In Application.Selection
        If IsDate(c.Value) Then
            MsgBox "C.Value: " & c.Value & vbCrLf & _
                    "Str:  " & Str(c.Value)
            c.Value = "'" & Str(c.Value)
            count = count + 1
        End If
    Next c
End Sub

If you would like to save this macro outside of the spreadsheet so that it can be used in other spreadsheets more easily, click on the File menu in the Visual Basic editor and select Export File and use the default name or give it a more meaningful name. To quickly import the macro into a different spreadsheet, use the Tools menu, Macro and Visual Basic Editor to open the editor, then select Import File from the File menu to add the Visual Basic module you saved earlier. All macros saved in that module will now be available to you.

To use this macro, select the range of cells in the spreadsheet that are to be converted. Then click on the Tools menu and select Macro and Macros. Double-click on the ConvertDatesToStrings macro, and it will perform the conversions for you.

For important updates related to this topic, refer to the following Rochester Support Center Technote document N1017021: Uploading Date Columns From Excel