Microsoft KB Archive/119404

{| = PRB: FoxPro Converts Excel TIME Formatted Data to Numeric =
 * width="100%"|

ID: Q119404

The information in this article applies to:


 * 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(, ) * Parameters:  Percentage of 24 hours *          :  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 . 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  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 Additional reference words: FoxWin 2.60 KBCategory: kbprg kbprb kbcode KBSubcategory: FxinteropSpread
 * }