php - PHPSpreadsheet formula not working between dates

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

A1 and A2 cells format is:

This is the result on web:

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();

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?

Answer

Solution:

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 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);

Source