format cell

H

hans

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 ?
 
L

Lancslad

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.
 
L

Lancslad

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.
 
R

Ron Rosenfeld

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 ?
Click to expand...

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
 
R

Ron Rosenfeld

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 ?
Click to expand...

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
Click to expand...

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
 
Top