27

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

pnuts
  • 58,317
  • 11
  • 87
  • 139
Abdul basit
  • 307
  • 1
  • 4
  • 9
  • the example you give is still not converting correctly - 12/5/2012 when converted from UK to ISO format should return 2012-05-12 – SeanC Jun 20 '12 at 13:18
  • 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 – Abdul basit Jun 20 '12 at 13:37
  • 1
    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() – Mark Baker Jun 20 '12 at 16:55

6 Answers6

100

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);
Hugo Dozois
  • 8,147
  • 12
  • 54
  • 58
Ahmed Eissa
  • 1,139
  • 1
  • 8
  • 14
  • 3
    Perfect. But could you please explain the significance of 25569 and 86400? – idok Mar 14 '13 at 16:10
  • 11
    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. – TJChambers Feb 06 '14 at 16:23
  • 1
    Can we use build-int function toFormattedString? echo PHPExcel_Style_NumberFormat::toFormattedString(42033, 'YYYY-MM-DD'); will output 2015-01-29 – Hắc Huyền Minh Mar 13 '15 at 11:28
  • 2
    Oh I needed this so bad couldn't work out what the hell kind of number excel was giving me. – Someone May 11 '16 at 10:05
  • 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. – QuidamAzerty Apr 25 '19 at 08:18
  • 1
    use `UNIX_DATE = round((EXCEL_DATE - 25569) * 86400)` to correct seconds. – David Vielhuber Jun 16 '20 at 15:00
30

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');
caponica
  • 3,788
  • 4
  • 32
  • 48
2

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');
Kevin Yan
  • 1,236
  • 11
  • 19
izn
  • 300
  • 1
  • 7
0

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');
SeanC
  • 15,695
  • 5
  • 45
  • 66
  • 1
    Fatal error: Call to a member function format() on a non-object it gives me this error i am using php 5.4.3 – Abdul basit Jun 22 '12 at 14:56
  • 1
    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' );` – SeanC Jun 22 '12 at 15:13
-1

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.

Netcleft
  • 1
  • 1
-3

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

Titi
  • 25
  • 6