CODEWITHSUNDEEP
javascriptphpexceldate
Dimitry
Dimitry

Reputation: 13

Convert excel DATEVALUE to javascript date

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?

Upvotes: 1

Views: 8767

Answers (3)

AdanCabanas
AdanCabanas

Reputation: 167

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

Upvotes: 1

ElefantPhace
ElefantPhace

Reputation: 3814

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;
}

Upvotes: 0

Gulim Shah
Gulim Shah

Reputation: 194

//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);

 }

Upvotes: 2

Related Questions