|
I am experiencing a rather strange problem when I set up a spreadsheet to compute the differences between two times using the spreadsheet::writeExcel perl module. The problem occurs when the times cross an hour boundry. For instance, 9:59 AM and 10:00 AM would show as 24.02 hours apart.
I can not reproduce the problem by typing the data and formula directly into excel. However, if I copy the generated data and formula to a different place in the spreadsheet, I get the same results. The only thing that I found that would cause it to calculate correctly is to edit either of the two data times manually. Even if you just change it to what it currently is it will still fix the problem. Any ideas?
Below I have attached a piece of code that will demonstrate the problem.
#!/usr/bin/perl -w
use strict;
use Spreadsheet::WriteExcel;
use Spreadsheet::WriteExcel::Utility;
my $workbook = Spreadsheet::WriteExcel->new("test2.xls");
my $worksheet1 = $workbook->add_worksheet();
my $timeFormat = $workbook->add_format(
size => 10,
valign => 'vcenter',
align => 'right',
num_format => 'hh:mm AM/PM',
);
my $numFormat = $workbook->add_format(
size => 10,
valign => 'vcenter',
align => 'right',
num_format => '0.00',
);
my $formula = '=IF(OR(ISBLANK('
. xl_rowcol_to_cell(2, 0)
. '),ISBLANK('
. xl_rowcol_to_cell(1, 0)
. ')),0,(('
. xl_rowcol_to_cell(2, 0)
. '-'
. xl_rowcol_to_cell(1, 0)
. '+('
. xl_rowcol_to_cell(2, 0)
. '<'
. xl_rowcol_to_cell(1, 0)
. ')))*24)';
# Add data and formula to worksheet.
$worksheet1->write(0, 0, "worksheet1" );
$worksheet1->write(1, 0, "9:59 AM", $timeFormat );
$worksheet1->write(2, 0, "10:00 AM", $timeFormat );
$worksheet1->write_formula(1, 1, $formula, $numFormat);
|
(19)
]
