Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula Help
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)) |
#2
|
|||
|
|||
Formula Help
On Mon, 7 Nov 2005 15:51:02 -0800, TDawg1831
wrote: 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 |
#3
|
|||
|
|||
Formula Help
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,F 3+G3*365.24,F3+(2*(G3*365.24))),"") "Ron Rosenfeld" wrote: On Mon, 7 Nov 2005 15:51:02 -0800, TDawg1831 wrote: 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 |
#4
|
|||
|
|||
Formula Help
On Tue, 8 Nov 2005 08:50:09 -0800, TDawg1831
wrote: 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" wrote: On Mon, 7 Nov 2005 15:51:02 -0800, TDawg1831 wrote: 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 Try this: =IF(I1="N",edate(F1,12*G1),edate(F1,12*CEILING(DAT EDIF(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 |
#5
|
|||
|
|||
Formula Help
On Tue, 8 Nov 2005 08:50:09 -0800, TDawg1831
wrote: 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" wrote: On Mon, 7 Nov 2005 15:51:02 -0800, TDawg1831 wrote: 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 Try this: =IF(I1="N",edate(F1,12*G1),edate(F1,12*CEILING(DAT EDIF(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(DAT EDIF(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),MO NTH( F1),MIN(DAY(F1),DAY(DATE(YEAR(F1)+CEILING(( DATEDIF(F1,TODAY(),"y")+1),5),MONTH(F1)+1,0))))) ------------------------------- --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula? | Excel Worksheet Functions | |||
writing a formula for a colored value | New Users to Excel | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Simplify formula | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |