PHPExcel detect if the cell value is formated to date or not

Question

Good day guys i have a problem on how to detect if the cell value is formated to date or not when i been upload a date value i use this code

$data = $objWorksheet->getCellByColumnAndRow($col, $row);

when a cell value is formated into date the value is $data = 27474 when is not $data = 12/2/1983 then i use this code to format their date

if(PHPExcel_Shared_Date::isDateTime($data)){
           $cellValue = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
           $dateValue = PHPExcel_Shared_Date::ExcelToPHP($cellValue);                        
           $dob =  date('Y-m-d',$dateValue);    
    }

now, the problem is when $data is in formated on date this code is OK but when $data is not formated the value is $data = 2036-02-18 which is wrong value

can anyone help me how to resolve this problem Thanks in Advance

Using `PHPExcel_Shared_Date::isDateTime()` is the correct method to identify whether a cell contains a date value or not, but it should also be noted that if a file has been loaded with `readDataOnly` set as `true`, then there is no way to identify a date value, because it's only the number format mask that differentiates a date value from a normal float value — Mark Baker, Feb 12 '15 at 11:33
Your other option is to call `getFormattedValue()` instead of `getValue()` once you know a cell contains a date, though that will return the date as a formatted string (using the same formatting as it is in MS Excel), which may not always be consistent with the way you want it formatted in your script — Mark Baker, Feb 12 '15 at 11:44
But if the cell simply contains a string value of (for example) `"12-Feb-2015"`, then this is just a string, and PHPExcel will not recognise it as a date — Mark Baker, Feb 12 '15 at 11:54
@MarkBaker Thanks to your comment i realize about the getFormattedValue() — itsmecidz, Feb 13 '15 at 07:46

score 3 · Answer 1 · answered Feb 12 '15 at 09:40

Please try this, this should work,

if(PHPExcel_Shared_Date::isDateTime($data)){
   $cellValue = $objWorksheet->getCellByColumnAndRow($col, $row);
   $InvDate= $cellValue->getValue();

   if(PHPExcel_Shared_Date::isDateTime($cell)) {
   $InvDate = date($format, PHPExcel_Shared_Date::ExcelToPHP($InvDate)); 
}  
}

score 2 · Answer 2 · edited Apr 20 '16 at 15:13

Thanks to your help guys, however this turned out to be the solution to my problem:

$data = $objWorksheet->getCellByColumnAndRow($col, $row);   

if(!strtotime($data)) {
    if(PHPExcel_Shared_Date::isDateTime($data)) {
        $cellValue = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
        $dateValue = PHPExcel_Shared_Date::ExcelToPHP($cellValue);                       
        $dateh     = date('Y-m-d',$dateValue);                          
    } else {                        
        $dateh  = "";                                                   
    } else {
        $st = strtotime($data);
        $dateh = date('Y-m-d',$st);                         

    }               
}

score 0 · Answer 3 · answered Sep 14 '20 at 20:28

and just because I ran into this...I used the strtotime method but I was pulling data for processing through mssql so i just did this

if(!strtotime($r['effective_date'])) {
            $r['effective_date'] = \Carbon\Carbon::parse(\PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($r['effective_date']))->format('Y-m-d');
        }

PHPExcel detect if the cell value is formated to date or not

3 Answers3