Excel date conversion using PHP Excel

Question

i am reading date from excel which is in this format 12/5/2012 day/month/year using this code to read . using PHP EXCEL

   PHPExcel_Style_NumberFormat::toFormattedString($value['A'],'YYYY-MM-DD' );

its working like charm converting the above date '12/5/2012' to '2012-12-05'

now the problem is if the date is lets says 18/5/2012 or you can say if i set day greater than 12 it gives me this date 18/5/2012 in this format 18/5/2012 after formating

i tried this thing as well

      $temp  = strtotime(  PHPExcel_Style_NumberFormat::toFormattedString($value['A'],'YYYY-MM-DD' );
      $actualdate = date('Y-m-d',$temp) ;

THis is also converting the date '12/5/2012' correct but in this case 18/5/2012 it gives output as 1970-01-01

Answer 1

If you're using python I resolved this issue by add xldate class from xlrd lib, here I show you the code (This is in Odoo 10 module):

from xlrd import open_workbook, xldate
wb = open_workbook(file_contents=excel_file)
data_sheets = []

        # Parse all data from Excel file
        for s in wb.sheets():
            data_rows = []
            headers = []
            for row_key, row in enumerate(range(s.nrows)):
                if row_key != 0:
                    data_row = {}
                    for index, col in enumerate(range(s.ncols)):
                        value = s.cell(row, col).value
                        key = headers[int(index)]
                        if key == 'Date' and (isinstance(value, float) or isinstance(value, int)):
                            value = xldate.xldate_as_datetime(value, wb.datemode)
                            data_row[key] = value
                        else:
                            data_row[key] = value

                    data_rows.append(data_row)
                else:
                    for index, col in enumerate(range(s.ncols)):
                        value = (s.cell(row, col).value)
                        headers.append(value)
            data_sheets.append(data_rows)

value = xldate.xldate_as_datetime(value, wb.datemode) will return datetime object with correct values

Answer 2

It appears your variable is a string, or is expecting a US format date.
use 'DateTime::createFromFormat' to cast the date into an actual date format

$date = DateTime::createFromFormat('d/m/y', $value['A']);
echo $date->format('Y-m-d');

Answer 3

An easy way...

<?php
    $date = date_create('30-12-1899');

    date_add($date, date_interval_create_from_date_string("{$value['A']} days"));
    echo date_format($date, 'Y-m-d');

Answer 4

Please use this formula to change from Excel date to Unix date, then you can use "gmdate" to get the real date in PHP:

UNIX_DATE = (EXCEL_DATE - 25569) * 86400

and to convert from Unix date to Excel date, use this formula:

EXCEL_DATE = 25569 + (UNIX_DATE / 86400)

After putting this formula into a variable, you can get the real date in PHP using this example:

$UNIX_DATE = ($EXCEL_DATE - 25569) * 86400;
echo gmdate("d-m-Y H:i:s", $UNIX_DATE);

Answer 5

When using PHPExcel you can use the built in function:

$excelDate = $cell->getValue(); // gives you a number like 44444, which is days since 1900
$stringDate = \PHPExcel_Style_NumberFormat::toFormattedString($excelDate, 'YYYY-MM-DD');