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

Noh

the example you give is still not converting correctly - 12/5/2012 when converted from UK to ISO format should return 2012-05-12 – Eskilstuna

yes youre right havent noticed that .....in order to get in this format 2012-05-12 i have to make string format like this YYYY-DD-MM – Noh

Is the value an Excel DateTimestamp or is it a string? If the former, why not use the PHPExcel_Shared_Date conversion methods like ExcelToPHP() or ExcelToPHPObject()? If the latter, then you shouldn't use toFormattedString() – Arnulfo

Answer 1

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

Baccivorous

Perfect. But could you please explain the significance of 25569 and 86400? – Giulietta

I can. The 86400 is number of seconds in a day = 24 * 60 * 60. The 25569 is the number of days from Jan 1, 1900 to Jan 1, 1970. Excel base date is Jan 1, 1900 and Unix is Jan 1, 1970. UNIX date values are in seconds from Jan 1, 1970 (midnight Dec 31, 1969). So to convert from excel you must subtract the number of days and then convert to seconds. – Convince

Can we use build-int function toFormattedString? echo PHPExcel_Style_NumberFormat::toFormattedString(42033, 'YYYY-MM-DD'); will output 2015-01-29 – Mohican

Oh I needed this so bad couldn't work out what the hell kind of number excel was giving me. – Scrivenor

This answer is incomplete for all Excel dates as they can have a comma with the faction of a day after the number of days since 01/01/1900. – Lemberg

use UNIX_DATE = round((EXCEL_DATE - 25569) * 86400) to correct seconds. – Alluvion

Answer 2

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

Jacobsohn

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

Obduliaobdurate

Answer 4

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

Eskilstuna

Fatal error: Call to a member function format() on a non-object it gives me this error i am using php 5.4.3 – Noh

if the echo doesn't work, then it's a minor problem - PHPExcel_Style_NumberFormat::toFormattedString(DateTime::createFromFormat('d/m/y', $value['A']),'YYYY-MM-DD' ); – Eskilstuna

Answer 5

Considering that 1664193600000 = 26.09.2022 12:00:00 the answer from Excel to Unix is:

UNIX_DATE = (EXCEL_DATE - 25569) * 86400000

and from UNIX to Excel

EXCEL_DATE = 25569 + (UNIX_DATE / 86400000)

The UNIX_DATE in PHP is given in ms.

86400000ms correspond to 24 h/d * 3600 s/h * 1000 ms/s.

25569 is the number of days from Jan 1, 1900 to Jan 1, 1970.

Heliolatry

Answer 6

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

Danseur