HELP....

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

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)
 
Hi Alicia..............

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

2. =H1+365

Vaya con Dios,
Chuck, CABGx3





Alicia said:
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
Click to expand...
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
Click to expand...
to calculate 12 months from the current rent increase column H
 
Back
Top