How to format dates from a file containing different formats in PHP?

Question

I have a excel file which has dates in multiple formats in no particular order. The formats vary

dd/mm/yyyy
dd/mm/yy
dd.mm.yyyy
dd.mm.yy
dd-mm-yyyy
dd-mm-yy
dd.Jan.18
dd-Jan-2018

I loop through the excel rows and fetch the dates one by one. How can I convert these dates to a particular format? Preferably yyyy-mm-dd I'm using PHP and storing the dates into mysql after processing.

I have tried this method but it doesn't work for dd.mm.yy

$date = $row[$datepos];
$date = str_replace('/', '-', $date);
$date = date("Y-m-d",strtotime($date));

Are those all the formats you could encounter or are more variations possible? — Mast, Aug 19 '18 at 07:00
Just [str_replace](http://php.net/manual/en/function.str-replace.php) `.` and `-` with `/` — Lawrence Cherone, Aug 19 '18 at 07:04
What if you add an extra row between row 2 and 3: `$date = str_replace('.', '-', $date);` — Mast, Aug 19 '18 at 07:04
Is that the final list of date variations or will there be more in the future? The trouble with dates is that while your current list seems to actually be pretty uniform, there often are cases where dates are in the US format (mm-dd-yy) or in the Chinese format (yy-mm-dd), and that's when things get hairy. — dearsina, Aug 19 '18 at 07:12
Yes this is the historic data. So there will be no more date formats to look for in future. — Lucifyer, Aug 19 '18 at 07:15

dearsina · Accepted Answer · 2018-08-19T07:47:18.457

If those are the only dates you have to deal with, the following code will work:

$dates = [
    "01/01/2018",
    "01/01/18",
    "01.01.2018",
    "01.01.18",
    "01-01-2018",
    "01-01-18",
    "01.Jan.18",
    "01-Jan-18",
];

foreach($dates as $date){
    $dt_array = preg_split("/[^A-Za-z0-9]/", $date);
    //Break apart the date string using any non-alphanumeric character as the delimiter

    var_dump($dt_array);
    //Just for demonstration purposes

    $day = $dt_array[0];
    // Grab the day

    if(is_numeric($dt_array[1])){
        $month = $dt_array[1];
    } else {
        $dp = date_parse($dt_array[1]);
        $month = $dp['month'];
    }
    //The month is a little bit more complex,
    //because at times it's an integer, at times it's a string,
    //and we want it to always be a integer

    $year = $dt_array[2];
    //Grab the year

    $dt = new DateTime("{$year}-{$month}-{$day}");
    //The y-m-d format is flexible,
    //because it will accept yyyy and yy
    //and since your m and d are integers,
    //it will work even if they don't have a leading zero.

    var_dump($dt->format('Y-m-d'));
    //Just for demonstration purposes
}

Great! Just keep in mind this won't work for US format dates (mm/dd/yy). — dearsina, Aug 19 '18 at 07:48

score 0 · Answer 2 · answered Aug 19 '18 at 07:19

0

You can try this.

$date=date_create(pass the date from your excel); date_format($date,"Y/m/d H:i:s");

answered Aug 19 '18 at 07:19

Kiran G

21
3

Tried it, `Parse error: syntax error, unexpected 'the' (T_STRING), expecting ')' in ` – Lawrence Cherone Aug 19 '18 at 07:24
$array = array("-","."); $date = str_replace($array,'/','10.12.2014'); $date = date_create($date); echo date_format($date,'Y-m-d'); Output:2014-10-12 @kiran-g – Lucifyer Aug 19 '18 at 07:30

How to format dates from a file containing different formats in PHP?

2 Answers2