Yearfrac not working

Topics: Developer Forum, Project Management Forum, User Forum
Jul 22, 2012 at 9:46 AM

Hello,

Have following piece of code, but the YEARFRAC formulas are not shown up in the created excel file, rest of the formulas are imported. The formulas are working direct in excel.

Have someone a clear moment, what is happening.

// set timezone
date_default_timezone_set('Europe/London');

// include
require_once 'phpexcel/Classes/PHPExcel.php';

// create object
$objPHPExcel = new PHPExcel();

// set active sheet
$objPHPExcel->setActiveSheetIndex(0);

// create sheet object
$objSheet = $objPHPExcel->getActiveSheet();

// set title
$objSheet->setTitle('Step 1');

// check date
$objSheet->setCellValue('C8', '2012-06-31');

// create new sheet
$objPHPExcel->createSheet();

// set active sheet
$objPHPExcel->setActiveSheetIndex(1);

// create sheet object
$objSheet = $objPHPExcel->getActiveSheet();

// set title
$objSheet->setTitle('Step 2');

// parameter
$objSheet->setCellValue('C10', 'A');

// start date
$objSheet->setCellValue('G10', '2008-09-01');

// end date
$objSheet->setCellValue('H10', '');

// this line is a problem is not added
$objSheet->setCellValue('O10', '=IF(OR(C10="A",C10="B"),IF(YEARFRAC(G10,\'Step 1\'!$C$8-365)>3,0,MIN(YEARFRAC((\'Step 1\'!$C$8-365),(G10+1095)),1)),1)');

// this is working and added
$objSheet->setCellValue('P10', '=IF(OR(YEAR(G10) = 1900,G10<=TODAY()),1,0)');

// this is working and added
$objSheet->setCellValue('Q10', '=IF(OR(YEAR(H10) = 1900,H10>=\'Step 1\'!$C$8),1,0)');

// this line is a problem is not added
$objSheet->setCellValue('R10', '=YEARFRAC(MAX((\'Step 1\'!$C$8-365),G10),MIN(\'Step 1\'!$C$8,H10),1)');

// save as excel 2007
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

// no calculation
$objWriter->setPreCalculateFormulas(false);

// save
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));

TIA,

Martijn

 

 

Coordinator
Jul 22, 2012 at 11:28 AM

Your problem is not YEARFRAC

$objSheet->setCellValue('G10', '2008-09-01');

You're setting G10 as a string, not as a date. If you use the advanced value binder, or convert '2008-09-01' to a date using either the PHPExcel date conversion functions, or Excel's own Date conversion functions, your problem should be resolved.