I am reading excel data using php and JavaScript. Storing results in variable and showing it on the page.

Simple code example:

var yearend = "< ? php echo ($connection->sheets[0]["cells"][2][5]); ? >";

This works for text and fields with number. But when I format cell as "Date" it returns the values, such as.

Excel field is: 31-Dec-2015 - JavaScript returns value: 40542

I know it is a MS DATEVALUE formatting.

But i need to convert it to date using JavaScript so it shows 31-Dec-2015 or 31 December 2015 only.

So in short:

From Excel 40542 to JavaScript 31 December 2015.

Also, I only need as above, without trailing time and locations, so removing:

00:00:00 00:00 GMT

Also is it possible modify the date to +1 day or -1 day?

I am reading excel data using php and JavaScript. Storing results in variable and showing it on the page.

Simple code example:

var yearend = "< ? php echo ($connection->sheets[0]["cells"][2][5]); ? >";

This works for text and fields with number. But when I format cell as "Date" it returns the values, such as.

Excel field is: 31-Dec-2015 - JavaScript returns value: 40542

I know it is a MS DATEVALUE formatting.

But i need to convert it to date using JavaScript so it shows 31-Dec-2015 or 31 December 2015 only.

So in short:

From Excel 40542 to JavaScript 31 December 2015.

Also, I only need as above, without trailing time and locations, so removing:

00:00:00 00:00 GMT

Also is it possible modify the date to +1 day or -1 day?

• javascript
• php
• excel
• date

• datetime::createfromformat and datetime::format, basically. load the excel date string into a DateTime object, then you can format it however you'd like. – Marc B Commented Jul 10, 2015 at 14:24
• Quick and dirty formula to convert an MS Excel date/time to a Unix timestamp value – Mark Baker Commented Jul 10, 2015 at 14:25

3 Answers 3

//Convert Excel dates into JS date objects

//@param excelDate {Number}
//@return {Date}

function getJsDateFromExcel(excelDate) {

 // JavaScript dates can be constructed by passing milliseconds
 // since the Unix epoch (January 1, 1970) example: new Date(12312512312);

 // 1. Subtract number of days between Jan 1, 1900 and Jan 1, 1970, plus 1  (Google "excel leap year bug")             
// 2. Convert to milliseconds.

 return new Date((excelDate - (25567 + 1))*86400*1000);

 }

try this

toDate(serialDate, time = false) {
    let locale = navigator.language;
    let offset = new Date(0).getTimezoneOffset();
    let date = new Date(0, 0, serialDate, 0, -offset, 0);
    if (time) {
        return serialDate.toLocaleTimeString(locale)
    }
    return serialDate.toLocaleDateString(locale)
}

Use the following php function to covert the datevalue into a php timestamp. You could then use standard date functions to format however you wish

function xl2timestamp($xl_date){
    return ($xl_date - 25569) * 86400;
}