Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Perl Programming Language

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

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.
--

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

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc