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

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

regards,
sweet_dreams

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


"hans" wrote:

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 ?

=IF(B1<=0,0,B1) formatted as date dd-mm-yy


Vaya con Dios,
Chuck, CABGx3



"hans" wrote:

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 ?

To force a zero in A1, you could multiply the value by the result of an
argument. So in A1 enter =B1*(Date1<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.

"hans" wrote:

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 ?

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.

"Lancslad" wrote:

To force a zero in A1, you could multiply the value by the result of an
argument. So in A1 enter =B1*(Date1<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.

"hans" wrote:

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 ?

On Mon, 23 Jan 2006 03:51:02 -0800, "hans"
wrote:

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 ?

The problem is that A1 is formatted as "date". Therefore when the value is 0,
you will see the result you get.

Try this custom format instead:

Format/Cells/Number/Custom Type: [0]m/d/yyyy;[<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
wrote:

On Mon, 23 Jan 2006 03:51:02 -0800, "hans"
wrote:

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 ?

The problem is that A1 is formatted as "date". Therefore when the value is 0,
you will see the result you get.

Try this custom format instead:

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

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


--ron

I should rephrase my last statement:

"With this format, you won't even need to test for negative values in your
formula in A1" **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