Thursday, 3 September 2020

PHPSpreadsheet formula not working between dates

I want to calculate difference between 2 datetimes in hours with PHPSpreadsheet. This is how Excel does it:

enter image description here

A1 and A2 cells format is:

enter image description here

This is the result on web:

enter image description here

When I change value through PHPSpreadsheet, I get #VALUE! and different value formatting.

$reader = PhpSpreadsheet\IOFactory::createReader("Xlsx");
$target_file = __DIR__ . '/test.xlsx';
$spreadsheet = $reader->load($target_file);
$spreadsheet->getActiveSheet()->setCellValue('A1', '24.6.2020 12:30');
$writer = new PhpSpreadsheet\Writer\Html($spreadsheet);
$output = $writer->generateHTMLHeader();
$output .= $writer->generateStyles(true);
$output .= $writer->generateSheetData();
$output .= $writer->generateHTMLFooter();
$doc = new DOMDocument();
@$doc->loadHTML($output);
echo $doc->saveHTML();

enter image description here

I also tried with formatting like this 6/24/2020 14:30 but the result was same (#VALUE!)

$spreadsheet->getActiveSheet()->setCellValue('A1', '6/24/2020 14:30');

Anyone got any idea on how this should be done?



from PHPSpreadsheet formula not working between dates

No comments:

Post a Comment