Convert Excel serial number date to VFP date

Submitted by Sergey on January 1, 2008 - 22:18 UTC
topic: 
excel
code
vfp
foxpro
date

Excel stores dates as the number of days since 1900-01-00. The number 1 represents 1900-01-01 and so on. The time part, if present, is represented as decimal portion. There's a bug in implementation of that sequence. It assumes that there was 1900-02-29 but 1900 is not a leap year. As result the code below correctly convert dates starting with 1900-03-01 only.

For more info about Excel dates see How to use dates and times in Excel and Dates And Times In Excel

.

$SAMPLECODE$





 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23






* Example
? ExcelSerialNumber2Date(39278)			&& 2007-07-15
? ExcelSerialNumber2Datetime(39278.456777)	&& 2007-07-15 10:57:46
? ExcelSerialNumber2Time(.456777)		&& 10:57:46
RETURN
*--------------------------------------------------------------------

* Convert Excel serial number to a date 
FUNCTION ExcelSerialNumber2Date(tnExcelSerialNumber)
RETURN {^1899/12/30} + tnExcelSerialNumber 

* Convert Excel serial number to a datetime 
FUNCTION ExcelSerialNumber2Datetime(tnExcelSerialNumber)
RETURN DTOT({^1899/12/30} + INT(tnExcelSerialNumber)) + ;
	ROUND(24*60*60 * (tnExcelSerialNumber % 1),0)

* Convert Excel serial number to a time string
FUNCTION ExcelSerialNumber2Time(tnExcelSerialNumber)
RETURN SUBSTR(TTOC({^2000/01/10 00:00:00} + ROUND(24*60*60 * (tnExcelSerialNumber % 1),0),3), 12)

$SEEALSO$ Convert Unix time to datetime and Converting duration in seconds to time format

Comments