Convert Excel s 41014 date to actual date in PHP or JavaScript

0 votes

I'm developing a PHP/Jquery application that enables users to upload Excel spreadsheets directly from Excel. The values from each cell in the spreadsheet are then assigned to a variable, however for some reason, I'm having trouble with dates. In Excel, any date is entered as a number rather than 04/15/2012, such as 41014. (as if I were to format it in Excel as text).

How can I change this to the same YYYY-MM-DD format as the other data in the MySQL database I'm working with? I can accomplish it in PHP or jQuery, but jQuery seems to be more user-friendly.

Excel's Cell

04/15/2012

PHP's echo json_encode($var);

`{dateReceived: 41014}`

jQuery's console.log(dateReceived);

41014
Oct 24, 2022 in Others by Kithuzzz
• 38,000 points
4,437 views

1 answer to this question.

0 votes

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

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()
answered Oct 24, 2022 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

Convert a date format in PHP

Use strtotime() and date(): $originalDate = "2010-03-21"; $newDate ...READ MORE

answered Feb 17, 2022 in Others by Aditya
• 7,680 points
3,876 views
0 votes
0 answers

How to convert a string to an integer in JavaScript?

How do I convert a string to ...READ MORE

May 2, 2022 in Others by Kichu
• 19,040 points
520 views
0 votes
0 answers

How to insert dropdown or checkbox in Excel using Javascript API?

I'm following the doc (https://dev.office.com/reference/add-ins/excel/excel-add-ins-reference-overview) to build ...READ MORE

Oct 31, 2022 in Others by Kithuzzz
• 38,000 points
596 views
0 votes
1 answer
0 votes
0 answers

How to run a PHP function due to a button click (with button 'data' passed)?

I manage some sortiment items on a ...READ MORE

May 28, 2022 in PHP by Kichu
• 19,040 points
3,978 views
0 votes
1 answer
0 votes
1 answer

How to access PHP var from external javascript file?

Hello @kartik, You don't really access it, you ...READ MORE

answered Jul 6, 2020 in Java-Script by Niroj
• 82,840 points
7,795 views
0 votes
1 answer

How do I escape a single quote in SQL Server?

Hello @kartik, Single quotes are escaped by doubling ...READ MORE

answered Jul 21, 2020 in PHP by Niroj
• 82,840 points
6,240 views
0 votes
1 answer

Convert column in excel date format (DDDDD.tttt) to datetime using pandas

Given # s = df['date'] s 0 ...READ MORE

answered Oct 2, 2022 in Others by narikkadan
• 63,600 points
3,548 views
0 votes
1 answer

Convert text date/time to a real date time in excel

For a date conversion: =DATEVALUE(TEXT(A1,"MM/DD/YYYY")) For a time conversion: =TIMEVALUE(TEXT(A1,"HH:MM:SS")) For ...READ MORE

answered Dec 19, 2022 in Others by narikkadan
• 63,600 points
576 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP