Formula Help

[TDawg1831]

This is a tricky one but hopefully someone will be able to help. I need to
create a formula that can will give me an updated date based on dates. I
have a effective date and term of contract. Now if the contract is renewable
I want the formula to be able to look at the effective date and extend it out
by the term, say 5 years, and give me the new date if that day hasn't passed
the current day. If it has then extend the contract by the term again.

My thought is that only a very intricate IF function will work. I think I
have it but am having trouble getting into formula terms.

Here's what I have

=IF(I3="Y",=IF(Time Between the current date and the effective date is =<
the term, then the effective date plus the term,effective date),=IF(Time
between the current date and the effective date is =< the term, then the
effective date plus the term, effective date))

 

[Ron Rosenfeld]

This is a tricky one but hopefully someone will be able to help. I need to
create a formula that can will give me an updated date based on dates. I
have a effective date and term of contract. Now if the contract is renewable
I want the formula to be able to look at the effective date and extend it out
by the term, say 5 years, and give me the new date if that day hasn't passed
the current day. If it has then extend the contract by the term again.

My thought is that only a very intricate IF function will work. I think I
have it but am having trouble getting into formula terms.

Here's what I have

=IF(I3="Y",=IF(Time Between the current date and the effective date is =<
the term, then the effective date plus the term,effective date),=IF(Time
between the current date and the effective date is =< the term, then the
effective date plus the term, effective date))

Post some examples of possible data inputs and desired outputs.


--ron

 

[TDawg1831]

F1 = Effective Date "2/14/01"
G1 = Term (years) "5"
H1 = Formula Cell
I1 = Auto Renewal "Y" or "N"

Now based on the effective date if the term in years added to that date has
passed it needs to jump to the next possible date based on the term of the
contract. However, there are some lines that have a "N" for auto renewal and
need that end date not a new one 5 years out.

Here's one I got that does part of what I need. I can use it and go from
there manually but to have it do all would be better obviously. Thanks for
any help!!!!!!!

=IF(J3="y",IF(DATEDIF(F3,TODAY(),"d")<=G3*365.24,F3+G3*365.24,F3+(2*(G3*365.24))),"")

 

[Ron Rosenfeld]

F1 = Effective Date "2/14/01"
G1 = Term (years) "5"
H1 = Formula Cell
I1 = Auto Renewal "Y" or "N"

Now based on the effective date if the term in years added to that date has
passed it needs to jump to the next possible date based on the term of the
contract. However, there are some lines that have a "N" for auto renewal and
need that end date not a new one 5 years out.

Here's one I got that does part of what I need. I can use it and go from
there manually but to have it do all would be better obviously. Thanks for
any help!!!!!!!

=IF(J3="y",IF(DATEDIF(F3,TODAY(),"d")<=G3*365.24,F3+G3*365.24,F3+(2*(G3*365.24))),"")

Try this:

=IF(I1="N",edate(F1,12*G1),edate(F1,12*CEILING(DATEDIF(F1,A1,"y")+1,5)))

---------------------------
If the EDATE function is not available, and returns the #NAME? error, install
and load the Analysis ToolPak add-in.

How?

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.

 

[Ron Rosenfeld]

F1 = Effective Date "2/14/01"
G1 = Term (years) "5"
H1 = Formula Cell
I1 = Auto Renewal "Y" or "N"

Now based on the effective date if the term in years added to that date has
passed it needs to jump to the next possible date based on the term of the
contract. However, there are some lines that have a "N" for auto renewal and
need that end date not a new one 5 years out.

Here's one I got that does part of what I need. I can use it and go from
there manually but to have it do all would be better obviously. Thanks for
any help!!!!!!!

=IF(J3="y",IF(DATEDIF(F3,TODAY(),"d")<=G3*365.24,F3+G3*365.24,F3+(2*(G3*365.24))),"")

Try this:

=IF(I1="N",edate(F1,12*G1),edate(F1,12*CEILING(DATEDIF(F1,A1,"y")+1,5)))

---------------------------
If the EDATE function is not available, and returns the #NAME? error, install
and load the Analysis ToolPak add-in.

How?

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.
------------------------------

If you don't want to load the Analysis ToolPak, or corporate policy precludes
using it, you could use this formula:

=IF(I1="N",DATE(YEAR(F1)+G1,MONTH(F1),MIN(DAY(F1),
DAY(DATE(YEAR(F1)+G1,MONTH(F1)+1,0)))),DATE(
YEAR(F1)+CEILING((DATEDIF(F1,A1,"y")+1),5),MONTH(
F1),MIN(DAY(F1),DAY(DATE(YEAR(F1)+CEILING((
DATEDIF(F1,A1,"y")+1),5),MONTH(F1)+1,0)))))

I used A1 for testing purposes. However, for your purposes, you would want to
change A1 to TODAY().

------------------------------
=IF(I1="N",edate(F1,12*G1),edate(F1,12*CEILING(DATEDIF(F1,TODAY(),"y")+1,5)))

or

=IF(I1="N",DATE(YEAR(F1)+G1,MONTH(F1),MIN(DAY(F1),
DAY(DATE(YEAR(F1)+G1,MONTH(F1)+1,0)))),DATE(
YEAR(F1)+CEILING((DATEDIF(F1,TODAY(),"y")+1),5),MONTH(
F1),MIN(DAY(F1),DAY(DATE(YEAR(F1)+CEILING((
DATEDIF(F1,TODAY(),"y")+1),5),MONTH(F1)+1,0)))))