转换Excel的“41014”日期到PHP或JavaScript的实际日期 [英] Convert Excel's "41014" date to actual date in PHP or JavaScript
问题描述
41014
,而不是 04/15/2012
(好像我在Excel中格式化为 text
)。 如何将其转换为YYYY-MM-DD格式所以它适合我正在使用的其余的mySQL数据库?我可以在PHP或Jquery中执行,但是在jQuery中看起来似乎比较容易。
Excel的单元格
04/15/2012
PHP的 echo json_encode($ var);
`{dateReceived:41014}`
jQuery的 console.log(dateReceived);
41014
更新
我实际上无法获得这里提供的任何答案工作 - 我以为php的答案最初的工作,但由于某些原因,我无法得到它来输出我需要的,但我发现另一个简单的公式,我投入一个功能。如果其他人正在寻找一个类似问题的答案,这里是我所做的:(其中$ dateValue是Excel日期 41014
等...)
function convertDate($ dateValue){
$ unixDate =($ dateValue - 25569)* 86400;
return gmdate(Y-m-d,$ unixDate);
'其中Y是YYYY,m是MM,d是DD
}
直接从PHPExcel处理日期处理代码:
public静态函数ExcelToPHP($ dateValue = 0){
if(self :: $ ExcelBaseDate == self :: CALENDAR_WINDOWS_1900){
$ myExcelBaseDate = 25569;
//对于虚假的调整29-Feb-1900(第60天)
if($ dateValue< 60){
- $ myExcelBaseDate;
}
} else {
$ myExcelBaseDate = 24107;
}
//执行转换
if($ dateValue> = 1){
$ utcDays = $ dateValue - $ myExcelBaseDate;
$ returnValue = round($ utcDays * 86400);
if(($ returnValue< = PHP_INT_MAX)&&($ returnValue> = -PHP_INT_MAX)){
$ returnValue =(integer)$ returnValue;
}
} else {
$ hours = round($ dateValue * 24);
$ mins = round($ dateValue * 1440) - round($ hours * 60);
$ secs = round($ dateValue * 86400) - round($ hours * 3600) - round($ mins * 60);
$ returnValue =(整数)gmmktime($ hours,$ mins,$ secs);
}
//返回
return $ returnValue;
} //函数ExcelToPHP()
设置self :: $ ExcelBaseDate == self: :需要CALENDAR_WINDOWS_1900表示您正在使用的Excel基础日历:Windows 1900或Mac 1904 ...最有可能1900
如果您想要一个PHP DateTime对象相反:
public static function ExcelToPHPObject($ dateValue = 0){
$ dateTime = self :: ExcelToPHP DATEVALUE);
$ days = floor($ dateTime / 86400);
$ time = round((($ dateTime / 86400) - $ days)* 86400);
$ hours = round($ time / 3600);
$ minutes = round($ time / 60) - ($ hours * 60);
$ seconds = round($ time) - ($ hours * 3600) - ($ minutes * 60);
$ dateObj = date_create('1-Jan-1970 +'。$ days。'days');
$ dateObj-> setTime($ hours,$ minutes,$ seconds);
return $ dateObj;
} // function ExcelToPHPObject()
I'm writing something in PHP/Jquery that allows the user to upload an Excel spreadsheet from Excel. It then takes the data in that spreadsheet and assigns the values from each cell into a variable, but for some reason, I'm having a hard time with dates. Any date in Excel comes in as a number, like 41014
, instead of 04/15/2012
(as if I were to format in Excel as text
).
How do I convert this to a YYYY-MM-DD format so it fits in with the rest of the mySQL database I'm working with? I can do it either in PHP or Jquery, but doing it in jQuery seems easier to me.
Excel's Cell
04/15/2012
PHP's echo json_encode($var);
`{dateReceived: 41014}`
jQuery's console.log(dateReceived);
41014
Update
I couldn't actually get either of the answers provided here to work - I thought the php answer worked initially, but for some reason I couldn't get it to output what I needed, but I found another simple formula that I put into a function. In case anyone else is looking for an answer to a similar question, here's what I did:, (where $dateValue is the Excel Date 41014
etc...)
function convertDate($dateValue) {
$unixDate = ($dateValue - 25569) * 86400;
return gmdate("Y-m-d", $unixDate);
'where Y is YYYY, m is MM, and d is DD
}
Taken directly from the PHPExcel Date handling code:
public static function ExcelToPHP($dateValue = 0) {
if (self::$ExcelBaseDate == self::CALENDAR_WINDOWS_1900) {
$myExcelBaseDate = 25569;
// Adjust for the spurious 29-Feb-1900 (Day 60)
if ($dateValue < 60) {
--$myExcelBaseDate;
}
} else {
$myExcelBaseDate = 24107;
}
// Perform conversion
if ($dateValue >= 1) {
$utcDays = $dateValue - $myExcelBaseDate;
$returnValue = round($utcDays * 86400);
if (($returnValue <= PHP_INT_MAX) && ($returnValue >= -PHP_INT_MAX)) {
$returnValue = (integer) $returnValue;
}
} else {
$hours = round($dateValue * 24);
$mins = round($dateValue * 1440) - round($hours * 60);
$secs = round($dateValue * 86400) - round($hours * 3600) - round($mins * 60);
$returnValue = (integer) gmmktime($hours, $mins, $secs);
}
// Return
return $returnValue;
} // function ExcelToPHP()
Set self::$ExcelBaseDate == self::CALENDAR_WINDOWS_1900 as necessary to indicate the Excel base calendar that you're using: Windows 1900 or Mac 1904... most likely 1900
and if you want a PHP DateTime object instead:
public static function ExcelToPHPObject($dateValue = 0) {
$dateTime = self::ExcelToPHP($dateValue);
$days = floor($dateTime / 86400);
$time = round((($dateTime / 86400) - $days) * 86400);
$hours = round($time / 3600);
$minutes = round($time / 60) - ($hours * 60);
$seconds = round($time) - ($hours * 3600) - ($minutes * 60);
$dateObj = date_create('1-Jan-1970+'.$days.' days');
$dateObj->setTime($hours,$minutes,$seconds);
return $dateObj;
} // function ExcelToPHPObject()
这篇关于转换Excel的“41014”日期到PHP或JavaScript的实际日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!