Perl Programming Language :: Negative times in Spreadsheet::WriteExcel?


	Negative times in Spreadsheet::WriteExcel? 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(); -- These are my personal views and not those of Fujitsu Siemens Computers! Josef Mllers (Pinguinpfleger bei FSC) If failure had no penalty success would not be a prize (T. Pratchett) Company Details: http://www.fujitsu-siemens.com/imprint.html On Jun 5, 7:49 am, Josef Moellers <josef.moell@fujitsu-siemens.com> wrote: > 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 - Hide quoted text - Paul Lalli wrote: > On Jun 5, 7:49 am, Josef Moellers <josef.moell@fujitsu-siemens.com> > wrote: >>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 ####". 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 -- These are my personal views and not those of Fujitsu Siemens Computers! Josef Mllers (Pinguinpfleger bei FSC) If failure had no penalty success would not be a prize (T. Pratchett) Company Details: http://www.fujitsu-siemens.com/imprint.html On Jun 5, 12:49 pm, Josef Moellers <josef.moell...@fujitsu- siemens.com> wrote: > 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. -- - Hide quoted text - jmcnam@cpan.org wrote: > On Jun 5, 12:49 pm, Josef Moellers <josef.moell...@fujitsu- > siemens.com> wrote: >>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 -- These are my personal views and not those of Fujitsu Siemens Computers! Josef Mllers (Pinguinpfleger bei FSC) If failure had no penalty success would not be a prize (T. Pratchett) Company Details: http://www.fujitsu-siemens.com/imprint.html