Negative times in Spreadsheet::WriteExcel?

[Josef Moellers]

Hi,

I'm trying to record time differences in an Excel spreadsheet, but I'm
unable to do this for negative differences:

use strict;
use warnings;
use Spreadsheet::WriteExcel;
my $workbook = Spreadsheet::WriteExcel->new('timediff.xls');
my $HHMM = $workbook->add_format(
font => 'Arial',
size => 10,
num_format => 'hh:mm',
align => 'center',
border => 1,
);
my $worksheet = $workbook->add_worksheet('May');
my $diff = 10;
my $hhmm = sprintf('%02d', int($diff / 60))
. ':'
. sprintf('%02d', $diff % 60);
# The following is OK, prints "00:10"
$worksheet->write_date_time(0, 0, "T$hhmm", $HHMM);
# This prints "-T00:10"
$worksheet->write_date_time(1, 0, "-T$hhmm", $HHMM);
# This prints "T-00:10"
$worksheet->write_date_time(2, 0, "T-$hhmm", $HHMM);

$workbook->close();

 

[Paul Lalli]

I'm trying to record time differences in an Excel spreadsheet, but I'm
unable to do this for negative differences:

# The following is OK, prints "00:10"
$worksheet->write_date_time(0, 0, "T$hhmm", $HHMM);
# This prints "-T00:10"
$worksheet->write_date_time(1, 0, "-T$hhmm", $HHMM);
# This prints "T-00:10"
$worksheet->write_date_time(2, 0, "T-$hhmm", $HHMM);

I'm confused as to what you're going for. When I fire up Excel
itself, and try to enter a "negative" time (by either multiplying A1
from above by -1, or subtracting some value from it), I get a series
of "####" with a tooltip saying "negative dates or times are displayed
as ####".

If I understand correctly, you're hoping to display -70 minutes as
"-1:10", is that correct? I don't think Excel itself will do that.
This isn't a problem with Spreadsheet::WriteExcel...

Paul Lalli

 

[Josef Moellers]

I'm confused as to what you're going for. When I fire up Excel
itself, and try to enter a "negative" time (by either multiplying A1
from above by -1, or subtracting some value from it), I get a series
of "####" with a tooltip saying "negative dates or times are displayed
as ####".

Somehow ist must be able to do that, as we're supposed to use an Excel
spreadsheet to record flexitime and it is perfectly capable of
displaying that I left 2 minutes too early as "-0:02".

When I take a look at the format of the cell, it says
Category: Custom
Type: [h]:mm;[Red]-[h]:mm
and the formula in one of these cells is
=IF(A15>0;IF(A15<=NOW();K15-L15;0);0)

If I understand correctly, you're hoping to display -70 minutes as
"-1:10", is that correct? I don't think Excel itself will do that.
This isn't a problem with Spreadsheet::WriteExcel...

However, I'm unable to reproduce in an extremely simple sheet (A1=9:00,
B1=8:00, C1="=B1-A1", format as shown above), so I guess this is some
kind of Excel-wizardry and has nothing to do with Perl.

Thanks anyway for trying to help,

Josef

 

[jmcnamara]

Hi,

I'm trying to record time differences in an Excel spreadsheet, but I'm
unable to do this for negative differences:

Hi,

By default, Excel doesn't allow you to use negative times. You can see
some information about this here:

http://www.google.com/search?hl=en&q=excel+negative+time

One of the workarounds suggested in those links is to use the 1904
time epoch in Excel. Here is a Spreadsheet::WriteExcel example:

use strict;
use warnings;
use Spreadsheet::WriteExcel;

my $workbook = Spreadsheet::WriteExcel->new('timediff.xls');

# Use 1904 date epoch.
$workbook->set_1904();


my $HHMM = $workbook->add_format(
font => 'Arial',
size => 10,
num_format => 'hh:mm',
align => 'center',
border => 1,
);
my $worksheet = $workbook->add_worksheet('May');

my $diff = 10;

# Convert the minutes into a fraction of 24 hours
my $hhmm = $diff / 60 / 24;


$worksheet->write(0, 0, $hhmm, $HHMM);
$worksheet->write(1, 0, -$hhmm, $HHMM);

$workbook->close();

For further questions see the Spreadsheet::WriteExcel group:

http://groups.google.com/group/spreadsheet-writeexcel

John.
--

 

[Josef Moellers]

Hi,

I'm trying to record time differences in an Excel spreadsheet, but I'm
unable to do this for negative differences:

Hi,

By default, Excel doesn't allow you to use negative times. You can see
some information about this here:

http://www.google.com/search?hl=en&q=excel+negative+time

One of the workarounds suggested in those links is to use the 1904
time epoch in Excel. Here is a Spreadsheet::WriteExcel example:
[ ... ]

For further questions see the Spreadsheet::WriteExcel group:

http://groups.google.com/group/spreadsheet-writeexcel

Thanks, that indeed does the trick ... in Excel. When I view the sheet
in OpenOffice, it still shows some positive times within the cell and
"-00:10:00" in the edit line on top :-O
I am surprised that this isn't something that more people stumble across.

Josef