PHPSpreadsheet formula not working between dates

user_4553642 0
Viewed 323

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?

phpspreadsheet excel datetime php
1 Answers

In an Excel document, dates are stored as numbers, not strings. So you need to pass the correct number to setCellValue().

PhpSpreadsheet provides the utility method Date::stringToExcel() to convert strings to Excel dates. You can use it like this:

$date = PhpSpreadsheet\Shared\Date::stringToExcel('2020-06-24 12:30');
$spreadsheet->getActiveSheet()->setCellValue('A1', $date);
user_127639540
Related