PRB: FoxPro Converts Excel TIME Formatted Data to Numeric

• Microsoft FoxPro for Windows, version 2.6

SYMPTOMS

When you are APPENDing or IMPORTing FROM an .XLS file that has a field with the TIME format h:mm:ss into a character field in a .DBF file, the data will appear to be converted to a numeric equivalent in a character field.

For example, a worksheet cell formatted as TIME h:mm:ss in Microsoft Excel that contains 8:33:00 when appended into a FoxPro character field will appear as 0.35625. In fact, 8:33:00 is 35.625% of 24 hours.

The information that you see as "numeric" in the character field is in fact exactly how Microsoft Excel stores the data in its cells. The display that we see as 8:33:00 in Microsoft Excel is Microsoft Excel's representation of a TIME formatted field; that is, the Microsoft Excel cell actually contains 0.35625, not 8:33:00.

WORKAROUND

The following code sample may be of assistance for making the time data usable within FoxPro.

IMPORTANT: The following code has not been fully tested.

   * NUM2TIME.PRG
   * ------------
   *
   * Syntax    : NUM2TIME(<ExpN1>, <ExpN2>)
   * Parameters: <ExpN1> Percentage of 24 hours
   *           : <ExpN2> 12 or 24 (clock type)
   * Returns   : Character
   * Default   : 24 Hour clock
   *
   * This procedure accepts a numeric parameter that
   * represents a percentage of 24 hours. For example
   * it will convert .75000 to 18:00:00 or 06:00:00p
   * depending on the value of <ExpN2>. This is the
   * format that time data imported from a Microsoft
   * Excel spreadsheet will appear in for example.
   *
   * This function will provide granularity up to one
   * second. The number passed into m.Deltanum should
   * extend to five decimal places.
   *
   * VALID input range for <ExpN1> ranges from 0.0
   * through 0.99999.
   * ------------------------------------------------
   PARAMETERS m.deltanum, m.clocktype
   IF m.deltanum >= 0.00 AND m.deltanum <= 0.999999999999999
      m.hrs = ALLTRIM(STR(INT(((86400*m.deltanum)/60)/60)))
      m.min = ALLTRIM(STR(INT((86400*m.deltanum)/60);
         -(VAL(m.hrs)*60)))
      m.sec = ALLTRIM(STR(INT((86400*m.deltanum);
         -((VAL(m.hrs)*60)*60)-(VAL(m.min)*60))))
      IF m.clocktype = 12
         m.pm = .F.
         IF VAL(m.hrs) >12
            m.hrs = ALLTRIM(STR(VAL(m.hrs)-12))
            m.pm = .T.
         ENDIF
         m.ret = IIF(LEN(m.hrs)=2,m.hrs,'0'+m.hrs)+':';
            +IIF(LEN(m.min)=2,m.min,'0'+m.min)+':';
            +IIF(LEN(m.sec)=2,m.sec,'0'+m.sec);
            +IIF(m.pm,'p','a')
      ELSE
         m.ret = IIF(LEN(m.hrs)=2,m.hrs,'0'+m.hrs)+':';
            +IIF(LEN(m.min)=2,m.min,'0'+m.min)+':';
            +IIF(LEN(m.sec)=2,m.sec,'0'+m.sec)
      ENDIF
   ELSE
      m.ret = '**:**:**'
   ENDIF
   RETURN m.ret