Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > Negative times in Spreadsheet::WriteExcel?

Reply
Thread Tools

Negative times in Spreadsheet::WriteExcel?

 
 
Josef Moellers
Guest
Posts: n/a
 
      06-05-2007
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

 
Reply With Quote
 
 
 
 
Paul Lalli
Guest
Posts: n/a
 
      06-05-2007
On Jun 5, 7:49 am, Josef Moellers <(E-Mail Removed)>
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

 
Reply With Quote
 
 
 
 
Josef Moellers
Guest
Posts: n/a
 
      06-05-2007
Paul Lalli wrote:
> On Jun 5, 7:49 am, Josef Moellers <(E-Mail Removed)>
> 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

 
Reply With Quote
 
jmcnamara@cpan.org
Guest
Posts: n/a
 
      06-06-2007
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...+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.
--

 
Reply With Quote
 
Josef Moellers
Guest
Posts: n/a
 
      06-11-2007
http://www.velocityreviews.com/forums/(E-Mail Removed) 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...+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

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Forms Authentication Fails some times and not some times??? =?Utf-8?B?bWF2cmlja18xMDE=?= ASP .Net 0 03-28-2006 10:48 PM
SetAuthCookie works some times and fails some times? =?Utf-8?B?bWF2cmlja18xMDE=?= ASP .Net 0 03-23-2006 09:24 PM
Boot Times and Recycle Times Moo Digital Photography 3 11-20-2004 12:31 PM
Negative setup and Negative hold prem_eda VHDL 5 10-11-2004 12:14 PM
Why is the Constructor called 4 times but the Destructor 5 times? djskrill C++ 9 10-01-2003 07:18 PM



Advertisments