Convert excel DATEVALUE to javascript date

Question

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?

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
– 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
– Mark Baker, Commented Jul 10, 2015 at 14:25

Gulim Shah · Accepted Answer · 2015-07-10 14:26:59Z

2

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

 }

answered Jul 10, 2015 at 14:26

Gulim Shah

1947 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dimitry Over a year ago

Thank you for help. Above did convert the datevalue to java date, but it is wrong date. var yearend = new Date(("<?php echo ($connection->sheets[0]["cells"][2][5]); ?>" - (25569 - 1)) * 86400*1000); returns: Fri Dec 31 2010 00:00:00 GMT+0000 (GMT) It should be year 2015 not 2010. Any ideas?

Dimitry Over a year ago

Hi again, guys, i have managed to get the date, but now it comes up like Thu Apr 03 2014 01:00:00 GMT+0100 (BST) How do i format it to 3 April 2014?

AdanCabanas · Accepted Answer · 2019-06-04 20:17:34Z

1

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

answered Jun 4, 2019 at 20:17

AdanCabanas

1671 silver badge14 bronze badges

Comments

ElefantPhace · Accepted Answer · 2015-07-10 14:28:52Z

0

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

answered Jul 10, 2015 at 14:28

ElefantPhace

3,8143 gold badges22 silver badges36 bronze badges

Collectives™ on Stack Overflow

Convert excel DATEVALUE to javascript date

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related