Skip to content
Advertisement

Convert Excel’s “41014” date to actual date in PHP or JavaScript

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

JavaScript

PHP’s echo json_encode($var);

JavaScript

jQuery’s console.log(dateReceived);

JavaScript

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…)

JavaScript

Advertisement

Answer

Taken directly from the PHPExcel Date handling code:

JavaScript

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:

JavaScript
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement