Phpexcel date is reading wrong format

PHPphpexcel

I know there are a lot of questions similar to this but none of them actually has helped.

I'm having a cell in my excel file that I have to read.

Cell Date in Excel

and the format in the excel file is like below Date Format In Excel

In my php code I have it in a function that gets the sheet name and cell to read the cell .

$cellDate = $xls->getSheetByName($sheet)->getCell($cell)->getValue();

I've also tried this with getFormattedValue() and got same result

I then get the format with this

$format = $xls->getSheetByName($sheet)->getCell($cell)->getStyle()->getNumberFormat()->getFormatCode();

when I echo this out in PHP it shows the date as

13/4/2015

and for the format it shows

mm-dd-yyyy

where it show as dd-mm-yyyy

I now need to get Y-m-d format of these I've tried using all the available functions in PHPExcel but it's still the result is not correct.

I can't hard code the format because there are multiple cells with multiple formats and I need this to understand by itself

I've tried using

$objPHPExcel->getActiveSheet()
->getStyle('A1')
->getNumberFormat()
->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2 );

I really need this and I've already wasted 1 week on it.

Is there anyway to tell PHPEXCEL to parse whatever date is in the cell to Y-m-d format ?

UPDATE

As I was reading through the comments I went back and checked the function I wrote, I found that one of the cells [even] that it's formatted in Excel ,it's parsing a wrong format which in that case was a General Format;

I then got the value of getValue() and getFormattedValue() and compared these two if they are not the same that means it's formatted correctly and i can use PHPExcel to get dates from it.

BUT , (There is always a but). Thanks to Microsoft and its excel I'm still not able to differ 1st Feb 2016 and 2nd Of January ! which forever remains the question.

My only recommendation is to never trust excel formats !

Best Answer

If getValue() and getFormattedValue() both return a string containing 13/4/2015 then your cell content isn't a MS Excel date/time value but a standard string, and trying to apply a date format mask to a string won't achieve anything. PHPExcel's date formatting functions will only work if the cell content really is an MS Excel serialized datetime value, not simply string.

You'll have to use standard PHP functions like DateTime::createFromFormat() to convert the string value to a datetime object, and can then use the format() method to convert it to whatever format you want.

Alternatively, try passing the value to the PHPExcel_Shared_Date::PHPToExcel() to convert it to an MS Excel serialized timestamp; and setting that as the cell value, and then you can set a format mask and use getFormattedValue().

Related Solutions

PHPExcel Date Format

Is your problem in getting the date from MS SQL as a date/time, or setting the Excel date?

There is a whole section of the PHPExcel documentation that explains the use of the PHPExcel_Shared_Date::PHPToExcel($PHPDate) and PHPExcel_Shared_Date::FormattedPHPToExcel($year, $month, $day, $hours=0, $minutes=0, $seconds=0) helper methods for converting PHP dates to an Excel datetime stamp value that you set as the cell value, and then you apply a number format mask of one of the date masks such as PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2 to that cell

Instead of

$t_year     = substr($xls_column_datas["colname"],0,4);    
$t_month    = substr($xls_column_datas["colname"],5,2);    
$t_day      = substr($xls_column_datas["colname"],8,2);
$t_format   = '=date('.$t_format.')';
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($data_column_num, $data_row_num, $t_format );
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($data_column_num, $data_row_num)->getNumberFormat()->setFormatCode('[$-C09]d mmm yyyy;@');

try setting

$t_year   = substr($xls_column_datas["colname"],0,4);
$t_month  = substr($xls_column_datas["colname"],4,2);  // Fixed problems with offsets
$t_day    = substr($xls_column_datas["colname"],6,2);
$t_date   = PHPExcel_Shared_Date::FormattedPHPToExcel($t_year, $t_month, $t_day);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(
    $data_column_num, $data_row_num, $t_date 
);
$objPHPExcel->getActiveSheet()
    ->getStyleByColumnAndRow($data_column_num, $data_row_num)
    ->getNumberFormat()->setFormatCode(
        PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX14
    );

Php – Custom date format in PHPExcel

Problem is here:

$periodo = '15/'.$mese.'/'.$anno;
$periodo = strtotime($periodo);

which would give a string values of 15/1/2015, 15/2/2015... ``15/12/2015that you're then attempting to convert to a unix timestamp usingstrtotime()`.

If you read the PHP Docs on date formats used by the strtotime() function, you'll see that a / separator tells PHP that the date string is US date format.... i.e. mm/dd/yyyygiving a month value of15` in every case, and (of course) there is no month 15

Either use a dash (-) rather than a / to indicate European (dd-mm-yyyy) rather than US date format

$periodo = '15-'.$mese.'-'.$anno;
$periodo = strtotime($periodo);

or re-order the values to use US format

$periodo = $mese.'/'.'15/'.$anno;
$periodo = strtotime($periodo);

And for the second part of your question.... you can supply almost any format mask that MS Excel recognises, you're not restricted to the built-in formats; so:

$ews->getStyle('U1:EJ1')
    ->getNumberFormat()
    ->setFormatCode('mm/yyyy');

The format code is simply a string value

Best Answer

Related Solutions

PHPExcel Date Format

Php – Custom date format in PHPExcel

Related Topic