Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Font Colours / negative numbers
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Font Colours / negative numbers
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 "Andy" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Font Colours / negative numbers
Bernard,
You are a Life Saver!!! Thanks. That worked perfectly Andy "Bernard Liengme" wrote: 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 "Andy" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting text to negative numbers! | Excel Worksheet Functions | |||
Formula to ignore negative numbers in a column? | Excel Worksheet Functions | |||
Can I total only numbers with a specific font color? | Excel Discussion (Misc queries) | |||
Changing negative numbers to zero | Excel Discussion (Misc queries) | |||
Negative Numbers | Excel Discussion (Misc queries) |