Date Calculation (from entered date / 1yr later in next field)

[ajaminb]

In Excel, I want to be able to enter a date in one field and have a date that
is 1 year later in the field next to it; what formula may I use to achieve
this?

Also, if the resulting date is past, how do I make the date turn red?

And, if the first date is 8/30/2008, how do I make it that it turns Green
when we are three months before this date?

 

[muddan madhu]

Try this

suppose u enter date in A1
in B1 put this formula =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)) and drag it
till u need....

Select the col B | go to format | conditional formatting | condition
1- formula is : =B1<TODAY() | pcik the color | condition 2 - formula
is =B1>(TODAY()-90) | pick the color | ok

 

[ajaminb]

This is tremendously helpful. Condition 2 isn't working exactly as I'm
intending; it is making the color green (as I've selected) if it is any time
before the time of the date. My ideas is that the cell/field is black and the
font is white, if the date is past the date show, the date will turn red
(this is working). If the date show is long before it expires it will remain
white, at 90-day before the date is, it turns green. Any ideas?

 

[ShaneDevenshire]

Hi,

Here is a shorter formula:
suppose the first date is in A1 and in B1 you enter

=EDATE(A1,12)

This is an analysis toolpak function, so in 2003 and earlier you need to
choose Tools, Add-ins, and check Analysis ToolPak

 

[ajaminb]

Not sure why but this method didn't work for me.

 

[David Biddulph]

What do you mean by "did not work"? Did you get an error message, or an
unexpected result?
Did you look at Excel help for the EDATE function?

 

[ajaminb]

Yes, the end result was not the date one year from the other date. It had a
#NAME? in the field and, no, I did not try help because another person who
gave me another way to do it, their method worked, it is just a longer
formula.

Thank you,