phpexcel date is reading wrong format

Question

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.

and the format in the excel file is like below

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 !

Answer 1

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().

Answer 2

$objReader= PHPExcel_IOFactory::createReader('Excel2007');  // For excel 2007 
//Set to read only
$objReader->setReadDataOnly(true);        
//Load excel file
$objPHPExcel=$objReader->load(FCPATH.'assets/uploads/yourFolder/'.$file_name);        
$totalrows=$objPHPExcel->setActiveSheetIndex(0)->getHighestRow();   //Count Numbe of rows avalable in excel          
$objWorksheet=$objPHPExcel->setActiveSheetIndex(0);                
//loop from first data untill last data
for($i=2;$i<=$totalrows;$i++)
{   
$field0= $objWorksheet->getCellByColumnAndRow(0,$i)->getValue(); //Excel Column 1
$field1 = $objWorksheet->getCellByColumnAndRow(1,$i)->getValue(); //Excel Column 2
$field2= $objWorksheet->getCellByColumnAndRow(2,$i)->getFormattedValue(); //Excel Column 3
$date = PHPExcel_Shared_Date::ExcelToPHP($field2); //unix value
$field2= gmdate("Y-m-d", $date); //you can echo this value
}