Font Colours / negative numbers

[Guest]

Hi,

I have a spreadsheet that calculates working times, with a formula that
works out my daily flexi.

The problem that I have is in some cells 00:00 shows correctly in black,
however in other cells, it shows as -00:00 and it shows as Red font.

I have searched for cells containing conditional formatting and all these
have been cleared, and upon a second search now show that there are no cells
with conditional formatting. I have checked the formula to make sure that
the formatting has been carried down consistently and this is correct.

The format of the cell is set to custom... hh:mm_);[Red]-hh:mm_) So I can
sort of understand the red font as a minus is showing before 00:00, but my
question is, Why is it showing a zero time as -00:00, especially only in
certain cells?

TIA

Andy

 

[Bernard Liengme]

Because of rounding errors (converting decimal to binary and back) some
values that should be zero might be something like -.000000000000001 which
will display as -0:00
If the cells have a formula; replace it by =ROUND( <your_formula>, 10) and
see if this solves the problem
best wishes

 

[Guest]

Bernard,

You are a Life Saver!!!

Thanks. That worked perfectly

Andy

Because of rounding errors (converting decimal to binary and back) some
values that should be zero might be something like -.000000000000001 which
will display as -0:00
If the cells have a formula; replace it by =ROUND( <your_formula>, 10) and
see if this solves the problem
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

Hi,

I have a spreadsheet that calculates working times, with a formula that
works out my daily flexi.

The problem that I have is in some cells 00:00 shows correctly in black,
however in other cells, it shows as -00:00 and it shows as Red font.

I have searched for cells containing conditional formatting and all these
have been cleared, and upon a second search now show that there are no
cells
with conditional formatting. I have checked the formula to make sure that
the formatting has been carried down consistently and this is correct.

The format of the cell is set to custom... hh:mm_);[Red]-hh:mm_) So I can
sort of understand the red font as a minus is showing before 00:00, but my
question is, Why is it showing a zero time as -00:00, especially only in
certain cells?

TIA

Andy