Negative times in Spreadsheet::WriteExcel?

Discussion in 'Perl Misc' started by Josef Moellers, Jun 5, 2007.

  1. 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 Möllers (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
     
    Josef Moellers, Jun 5, 2007
    #1
    1. Advertising

  2. Josef Moellers

    Paul Lalli Guest

    On Jun 5, 7:49 am, Josef Moellers <>
    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
     
    Paul Lalli, Jun 5, 2007
    #2
    1. Advertising

  3. Paul Lalli wrote:
    > On Jun 5, 7:49 am, Josef Moellers <>
    > 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 Möllers (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
     
    Josef Moellers, Jun 5, 2007
    #3
  4. Josef Moellers

    Guest

    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.
    --
     
    , Jun 6, 2007
    #4
  5. 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 Möllers (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
     
    Josef Moellers, Jun 11, 2007
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Fabio

    Spreadsheet::WriteExcel

    Fabio, Mar 2, 2004, in forum: Perl
    Replies:
    2
    Views:
    902
    Tom Niesytto
    Mar 4, 2004
  2. Erica
    Replies:
    2
    Views:
    118
    John M. Gamble
    Sep 5, 2003
  3. Sven Jungnickel
    Replies:
    3
    Views:
    220
    John McNamara
    Jul 29, 2004
  4. Zhidian Du
    Replies:
    1
    Views:
    142
    A. Sinan Unur
    Dec 11, 2003
  5. Sean Berry

    Spreadsheet::WriteExcel

    Sean Berry, May 8, 2004, in forum: Perl Misc
    Replies:
    11
    Views:
    274
    Sean Berry
    May 9, 2004
Loading...

Share This Page