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

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




On Sep 27, 6:54*pm, ajaminb <ajam...@yahoo.com> wrote:
> 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?
> --
> 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?
--
AjaminB


"muddan madhu" wrote:

> 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
>
>
>
>
> On Sep 27, 6:54 pm, ajaminb <ajam...@yahoo.com> wrote:
> > 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?
> > --
> > AjaminB
>
>

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
--
Thanks,
Shane Devenshire


"ajaminb" wrote:

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

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


"ShaneDevenshire" wrote:

> 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
> --
> Thanks,
> Shane Devenshire
>
>
> "ajaminb" wrote:
>
> > 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?
> > --
> > 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,
--
AjaminB


"David Biddulph" wrote:

> 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?
> --
> David Biddulph
>
> "ajaminb" <(E-Mail Removed)> wrote in message
> news:171A58EC-7D97-42B2-AF42-(E-Mail Removed)...
> > Not sure why but this method didn't work for me.
> > --
> > AjaminB
> >
> >
> > "ShaneDevenshire" wrote:
> >
> >> 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
> >> --
> >> Thanks,
> >> Shane Devenshire
> >>
> >>
> >> "ajaminb" wrote:
> >>
> >> > 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?
> >> > --
> >> > AjaminB
>
>
>