software

I have a cell A1 in which I want to print a date dd-mm-yy. This cell is
depending on another cell B1 . In this cell I calculate the number of days by
distracting one date from another. If the number of days is positive A1
should read a date dd-mm-yy, if the number of days is negative A1 should read
0.
A1 gives me 0-1-1900
How can I get 0 in A1 ?

you should use IF function. for details check excel help or look he

spreadsheets.about.com/ cs/excelfunction1/a/iffunction.htm

pubs.logicalexpressions.com/P...cle.asp?ID=225

regards,
sweet_dreamsDear hans,

I think I know what's happening so check this out. The '0' is your negative
result in the format you defined. In your formula, you could further define
a negative result to be formatted differently than the positive result. Let
me know if this answer is not clear or you need more help.
--
Ruth Allen
EMBRACING TECHNOLOGY
embracingtech.comquot;hansquot; wrote:

gt; I have a cell A1 in which I want to print a date dd-mm-yy. This cell is
gt; depending on another cell B1 . In this cell I calculate the number of days by
gt; distracting one date from another. If the number of days is positive A1
gt; should read a date dd-mm-yy, if the number of days is negative A1 should read
gt; 0.
gt; A1 gives me 0-1-1900
gt; How can I get 0 in A1 ?

=IF(B1lt;=0,0,B1) formatted as date dd-mm-yyVaya con Dios,
Chuck, CABGx3
quot;hansquot; wrote:

gt; I have a cell A1 in which I want to print a date dd-mm-yy. This cell is
gt; depending on another cell B1 . In this cell I calculate the number of days by
gt; distracting one date from another. If the number of days is positive A1
gt; should read a date dd-mm-yy, if the number of days is negative A1 should read
gt; 0.
gt; A1 gives me 0-1-1900
gt; How can I get 0 in A1 ?

To force a zero in A1, you could multiply the value by the result of an
argument. So in A1 enter =B1*(Date1lt;Date2). So if Date1 is less than Date2,
A1 will equal B1. If not, the result will be multiplied by zero, so that will
set the result of the calc to zero.

Hope that helps.

quot;hansquot; wrote:

gt; I have a cell A1 in which I want to print a date dd-mm-yy. This cell is
gt; depending on another cell B1 . In this cell I calculate the number of days by
gt; distracting one date from another. If the number of days is positive A1
gt; should read a date dd-mm-yy, if the number of days is negative A1 should read
gt; 0.
gt; A1 gives me 0-1-1900
gt; How can I get 0 in A1 ?

Sorry Hans, my last post wasn't what you wanted.

I'd be tempted to do a conditional format so that if the value = 0, then the
font colour matches the background.

quot;Lancsladquot; wrote:

gt; To force a zero in A1, you could multiply the value by the result of an
gt; argument. So in A1 enter =B1*(Date1lt;Date2). So if Date1 is less than Date2,
gt; A1 will equal B1. If not, the result will be multiplied by zero, so that will
gt; set the result of the calc to zero.
gt;
gt; Hope that helps.
gt;
gt; quot;hansquot; wrote:
gt;
gt; gt; I have a cell A1 in which I want to print a date dd-mm-yy. This cell is
gt; gt; depending on another cell B1 . In this cell I calculate the number of days by
gt; gt; distracting one date from another. If the number of days is positive A1
gt; gt; should read a date dd-mm-yy, if the number of days is negative A1 should read
gt; gt; 0.
gt; gt; A1 gives me 0-1-1900
gt; gt; How can I get 0 in A1 ?

On Mon, 23 Jan 2006 03:51:02 -0800, quot;hansquot; gt;
wrote:

gt;I have a cell A1 in which I want to print a date dd-mm-yy. This cell is
gt;depending on another cell B1 . In this cell I calculate the number of days by
gt;distracting one date from another. If the number of days is positive A1
gt;should read a date dd-mm-yy, if the number of days is negative A1 should read
gt;0.
gt;A1 gives me 0-1-1900
gt;How can I get 0 in A1 ?

The problem is that A1 is formatted as quot;datequot;. Therefore when the value is 0,
you will see the result you get.

Try this custom format instead:

Format/Cells/Number/Custom Type: [gt;0]m/d/yyyy;[lt;0]\0;0

With this format, you won't even need to test for negative values in your
formula in A1.--ron

On Mon, 23 Jan 2006 09:45:27 -0500, Ron Rosenfeld gt;
wrote:

gt;On Mon, 23 Jan 2006 03:51:02 -0800, quot;hansquot; gt;
gt;wrote:
gt;
gt;gt;I have a cell A1 in which I want to print a date dd-mm-yy. This cell is
gt;gt;depending on another cell B1 . In this cell I calculate the number of days by
gt;gt;distracting one date from another. If the number of days is positive A1
gt;gt;should read a date dd-mm-yy, if the number of days is negative A1 should read
gt;gt;0.
gt;gt;A1 gives me 0-1-1900
gt;gt;How can I get 0 in A1 ?
gt;
gt;The problem is that A1 is formatted as quot;datequot;. Therefore when the value is 0,
gt;you will see the result you get.
gt;
gt;Try this custom format instead:
gt;
gt;Format/Cells/Number/Custom Type: [gt;0]m/d/yyyy;[lt;0]\0;0
gt;
gt;With this format, you won't even need to test for negative values in your
gt;formula in A1.
gt;
gt;
gt;--ron

I should rephrase my last statement:

quot;With this format, you won't even need to test for negative values in your
formula in A1quot; **IF** all you are concerned about is the appearance and not the
contents of the cell.

If you want a zero to be stored in A1, then you will need your IF formula, as
the formatting only changes the display of the cell and not the actual
contents.
--ron