ID: Q119404
The information in this article applies to:
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.
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
Last Reviewed: June 26, 1995