Creating formula in PHPExcel

0
votes

I am trying to calculate age based on birth date. Excel Formula is

=DATEDIF(K4,DATE(2014,3,31),"Y")=DATEDIF(K4,DATE(2014,3,31),"YM")

Which shows 9.7 if value in cell K is 2004-08-01 this is perfect but I am not able to set this formula in excel sheet using PHPExcel. Here is code what I am trying 

$date = date('2014,03,31');
for($i=3;$i<=100;$i++){
$objPHPExcel->getActiveSheet()-setCellValue('AQ'.$i,'=DATEDIF(K'.$i.',DATE('.$date.'),"Y")'.'& . & '.'DATEDIF(K'.$i.',DATE('.$date.'),"YM")');
}

What is wrong with the code why excel file not opening after download and showing fileformat or file extension not valid.

2
phpphpexcel
Is that a mistype on the question or the code? getActiveSheet()-setCellValue - MackieeE
yes, it looks like getActiveSheet()->setCellValue - Rohit Awasthi
@MackieeE Sorry its a typing mistake. - user1357093

2 Answers

1
votes

Your Excel formula concatenation is with an invalid .. Assuming you want to display a . between the Year and the Months then you need to treat it as a string literal ("."):

$date = date('2014,03,31');
for($i=3;$i<=100;$i++){
    $objPHPExcel->getActiveSheet()-setCellValue(
        'AQ'.$i,
        '=DATEDIF(K'.$i.',DATE('.$date.'),"Y")'.'& "." & '.'DATEDIF(K'.$i.',DATE('.$date.'),"YM")'
    );
}
0
votes

not tested, try this,

$date = date('2014,03,31');
for($i=3;$i<=100;$i++){
$objPHPExcel->getActiveSheet()->setCellValue('AQ'.$i,'=DATEDIF(K'.$i.',DATE('.$date.'),"Y")'.'& . & '.'DATEDIF(K'.$i.',DATE('.$date.'),"YM")');
}