I need to import an excel file into database using PHPExcel the issue is with the date

Question

I need to import an excel file into database using PHPExcel the issue is with the date,The date can be in any format in excel file.

when the date in excel file is in this format 12/12/2012 (m/d/y) it save correctly into database but if the format date in excel file is this 24/11/2014(d/m/y) it upload blank when 24/12/2012(d/m/y) the value is 2036-03-01

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

  $dob =  date('Y-m-d',$dateValue);                                         
} else{
  $datedata = "NULL";               
}

hope you can help me guyz

refer this link http://stackoverflow.com/questions/12362953/phpexcel-date-format — Anish, Oct 14 '14 at 04:32
What's the value of `$dateValue` when you read in dates in the `d/m/y` format? — ivan.sim, Oct 14 '14 at 05:24
i already search your answer and thanks for helping me but stil my problem didnt solved yet, yah your correct the format type of the cells in excel spreadsheet is incorrect so that is my limitation of my works THANKS i considered your answer — itsmecidz, Oct 17 '14 at 06:49
Cool. If your felt I answered your original question, please consider marking my answer as accepted. Then feel free to post a new question. Thanks! — ivan.sim, Oct 17 '14 at 07:00

score 2 · Answer 1 · edited Oct 14 '14 at 05:36

I would try formatting the data from the SELECT statement you are using. Let the DB do the time work. Let the PHP script to the real work.

Here is a link to the MYSQL Reference guide that explains all the many way to play with date and time on the database side.

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

What you are looking for is

SELECT date_format('2014-10-13 22:00:00, '%m-%d-%Y') from <table> WHERE <column> = <data>

this will output 10-13-2014

From here you should be able to figure out the rest.

score 2 · Accepted Answer · edited May 08 '18 at 21:48

The important thing here to be reminded of is that Excel stores dates and times as serial date or serial date-time. This is the number of days since Jan 0, 1900 plus a fraction of a 24 hour day. This article contains a good explanation.

The fact that you are getting a negative serial date like -454654643 probably mean that the formatt type of the cells in your Excel spreadsheet is incorrect.

There are two things you need to verify:

In Excel, use Ctrl + ` to reveal the serial date value of each date cell. None of these values should be negative. Then use Ctrl + ` to switch back to the default formatted-date view. (The ` key is the one above the tab key on your keyboard).
Right-click on the date cells, navigate to Format Cells > Number > Date > Type, and ensure that the date format specified in Type matches your intent. So if your dates are to be interpreted as d/m/y, make sure the Type matches. And likewise, for m/d/y.

Hope this helps.

I need to import an excel file into database using PHPExcel the issue is with the date

2 Answers2