Date format handling while reading from Excel in PHP

Question

I am reading from Excel and I am not sure what date format will be in the Excel file. I need it in the specific format of "Y-m-d H:i:s".

I have an example which I got from Excel:

17/03/2018 00:00 and 3/1/2018 0:37

 echo date("Y-m-d H:i:s", strtotime('17/03/2018 00:00'));   
 echo date("Y-m-d H:i:s", strtotime('3/1/2018 0:37'));

The result I got is:

first one : 1970-01-01 05:30:00
second one : 2018-03-01 00:37:00
in which the first is wrong and the second is correct. I know that it is not correctly reading the month but how can I handle this?

Can anyone help me with handling any format which comes from Excel?

It does not follow the indian standards. The correct format should be: strtotime('03/17/2018 00:00'). Or use DateTime::createFromFormat(). Also note that you can not convert any format to a date. format of the date should be already known. You can definitely use strtotime to convert almost every type of string to date but not any format — Aniket Sahrawat
– Aniket Sahrawat, Commented Mar 17, 2018 at 11:28
I understand your concern but date written by human in any format is ambiguous. For eg: 01-02-03, will you interpret as 1st Feb 2003 or 2nd Jan 2003 or 3rd Jan 2002? It is not possible to come to a conclusion. So the best you can do is try to convert string to timestamp using strtotime and if it fails to return a timestamp then let human handle it. — Aniket Sahrawat
– Aniket Sahrawat, Commented Mar 17, 2018 at 11:59

Nanhe Kumar · Accepted Answer · 2018-03-17 11:48:25Z

2

echo date("Y-m-d H:i:s", strtotime(str_replace('/','-',"17/3/2018 00:00"))); 

echo date("Y-m-d H:i:s", strtotime(str_replace('/','-','3/1/2018 0:37')));

answered Mar 17, 2018 at 11:48

Nanhe Kumar

16.5k5 gold badges85 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Aniket Sahrawat Over a year ago

Note: this approach only solves the given test cases and will fail to convert any format as required in OP.

Nanhe Kumar Over a year ago

@AniketSahrawat I know i have given the solution of first one : 1970-01-01 05:30:00 and i am assuming Indian date format d/m/yyyy

Aniket Sahrawat Over a year ago

Even if we consider that you gave solution for only indian date format, it is so unnecessary to first str_replace and then convert it to ts. I am sure parsing the string using DateTime::createFromFormat("d/m/Y H:i", "17/3/2018 00:00")->format("Y-m-d H:i:s"); must be way faster than doing this.

Collectives™ on Stack Overflow

Date format handling while reading from Excel in PHP

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related