HELP....

[Alicia]

I am working on an excel worksheet and have two issues.
1. I can not figure out how to make a statement that allows a cell if the calculation is a negative number to return a "0" in its place.
This is in J column Rent Difference.

2. I need to figure out how to calculate a next effective date in column M to calculate 12 months from the current rent increase column H

 

[Frank Kabel]

Hi
1. try
=MAX(your_formula,0)

2. Without taking leap years into account try:
=DATE(YEAR(H1)+1,MONTH(A1),DAY(A1))

To also deal with leap years (29th Feb becomes the 28th in the
following year try
=DATE(YEAR(H1)+1,MONTH(H1),DAY(H1)) -
(DAY(A1)<>DAY(DATE(YEAR(H1)+1,MONTH(H1),DAY(H1)))

 

[hgrove]

Frank Kabel wrote...
...

To also deal with leap years (29th Feb becomes the 28th in the
following year try
=DATE(YEAR(H1)+1,MONTH(H1),DAY(H1)) -
(DAY(A1)<>DAY(DATE(YEAR(H1)+1,MONTH(H1),DAY(H1)))

...

Yuck!

If you want one year after 29 Feb 2004 to be 28 Feb 2005, use

=A1+366-(DAY(A1)<>DAY(A1+366))

if you want it to map to 1 March 2005, use

=A1+365+(DAY(A1)<>DAY(A1+365)

 

[CLR]

Hi Alicia..............

1. =IF(YourFormula<0,0,YourFormula)

2. =H1+365

Vaya con Dios,
Chuck, CABGx3

I am working on an excel worksheet and have two issues.
1. I can not figure out how to make a statement that allows a cell if the

calculation is a negative number to return a "0" in its place.

This is in J column Rent Difference.

2. I need to figure out how to calculate a next effective date in column M

to calculate 12 months from the current rent increase column H

 

[Frank Kabel]

Frank Kabel wrote...

..
..

Yuck!

agreed: Time to stop working right now. thanks for the correction!
Frank