Custom date format in PHPExcel

Question

In my excel file I am building from PHP the first row is the header one. I need to put there several dates as column header. Each date is (in dd/mm/yyyy format) 15/mm/yyyy starting from 15/01/2007 and ending with 15/12/2018 stepping month by month. My code is this:

for($anno = $annoMin; $anno<=$annoMax; $anno++){
    for($mese = 1; $mese <= 12; $mese++){
        $mese = sprintf("%02s", $mese);
        $periodo = '15/'.$mese.'/'.$anno;
        $periodo = strtotime($periodo);
        $periodo = PHPExcel_Shared_Date::PHPToExcel($periodo);
        array_push($header_array,$periodo);
    }
}

I build the date as a string, convert it into a unix timestap, convert it into an excel date and push it into the $header_array. Then I draw the cells in the excel:

$ews->fromArray($header_array, ' ', 'A1');

Finally I format the cells where there is a date as:

$ews->getStyle('U1:EJ1')
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);

ISSUES ARE:

1. Actually my code is returning 'False' for each cell with a date;
2. I need to add a custom mask that is not listed in the PHPExcel library: MM/YYYY to be shown in each cell.

For the first issue: I have checked different SO questions and followed mainly this one. with no luck.

Answer 1

Problem is here:

$periodo = '15/'.$mese.'/'.$anno;
$periodo = strtotime($periodo);

which would give a string values of 15/1/2015, 15/2/2015... ``15/12/2015that you're then attempting to convert to a unix timestamp usingstrtotime()`.

If you read the PHP Docs on date formats used by the strtotime() function, you'll see that a / separator tells PHP that the date string is US date format.... i.e. mm/dd/yyyygiving a month value of15` in every case, and (of course) there is no month 15

Either use a dash (-) rather than a / to indicate European (dd-mm-yyyy) rather than US date format

$periodo = '15-'.$mese.'-'.$anno;
$periodo = strtotime($periodo);

or re-order the values to use US format

$periodo = $mese.'/'.'15/'.$anno;
$periodo = strtotime($periodo);

---

And for the second part of your question.... you can supply almost any format mask that MS Excel recognises, you're not restricted to the built-in formats; so:

$ews->getStyle('U1:EJ1')
    ->getNumberFormat()
    ->setFormatCode('mm/yyyy');

The format code is simply a string value