0

I read the date from excel using php and convert to sql format using following code

$date = date('Y-m-d', strtotime($cell));

but after convertion the date is not correct. how to solve this problem?

10
  • what is your $cell output and what is expecting? Commented May 12, 2014 at 7:14
  • what is the format of the date in $cell? in terms of dd-mm-yyy etc... Commented May 12, 2014 at 7:14
  • 1
    sample value from $cell needed Commented May 12, 2014 at 7:23
  • format of $cell is dd/mm/yyyy Commented May 12, 2014 at 7:30
  • Format of the cell is meaningless; it's the value in the cell that's important, and that should be an Excel serialized date/time value Commented May 12, 2014 at 7:57

3 Answers 3

2

The value store in PHPExcel is an Excel serialized datetime value (a float, representing the number of days since 1/1/1900 or 1/1/9004 depending on whether Windows or Mac Calendar was used). You need to convert this to a Unix timestamp or a PHP DateTime object to use PHP date functions against it for setting the format

Taken directly from the PHPExcel Date handling code:

function ExcelToPHP($dateValue = 0, $ExcelBaseDate = 1900) {
    if ($ExcelBaseDate == 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()

Set $ExcelBaseDate to 1900 or 1904 as necessary to indicate the Excel base calendar that you're using: Windows 1900 or Mac 1904

and if you want a PHP DateTime object instead:

function ExcelToPHPObject($dateValue = 0) {
    $dateTime = 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()
Sign up to request clarification or add additional context in comments.

2 Comments

is $ExcelBaseDate is the Excel serialized datetime value?
No, the $dateValue argument is your Excel serialized timestamp (34394); the $ExcelBaseDate identifies which calendar MS Excel is using, which can be either the Windows 1900 calendar (base date is 1st January 1900) or the Mac 1904 calendar (base date is 1st January 1904).... in your case, if 34394 is the 1st March 1994, your using the Windows 1900 calendar which is the more common; so you don't need to pass any $ExcelBaseDate argument as it will default to the Windows 1900 calendar
1

try

$date = str_replace('/', '-', $cell);
$start_date = date('Y-m-d', strtotime($date));

1 Comment

it changed the format but after convertion the dates are different
0

You can skip doing this, and ask MySQL to read whatever date format you have to its own DateTime type. Use STR_TO_DATE(yourdatefield, '%d/%m/%Y') in MySQL and optionally change the last argument to fit your needs... The last argument tells MySQL in what format to expect the date string.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.