Here's a puzzling one for you guys to help me out with.

[JJRudd]

Practice Dec Tracker.xls
	G	H	I	J
3	First Date Eligible	Last Date Received	Last Date Denied	Next Date Eligible
4	22-Feb-97	27-Mar-05		27-Mar-08
5	8-Jul-99	23-Jun-02		23-Jun-05
6	23-Oct-01			23-Oct-01
7	23-Mar-03			23-Mar-03
8	24-Apr-04			24-Apr-04
9	24-Nov-04			24-Nov-04
10	7-Jan-05			7-Jan-05
11	13-Jan-05	27-Sep-04		27-Sep-07
EPR Tracker

J1=IF(H4="",G4,(DATEVALUE(MONTH(H4)&"/"&DAY(H4)&"/"&YEAR(H4)+3)))

Here's a fun one. You can see the formula in J1 that I'm using to show a date 3 years beyond what's in H1 if H1 is filled. If it's not, then it shows G1. Now, I'm wanting to modify the formula to show a date in J1 that is 1 year beyond a date entered in I1 whether or not a date is entered in H1.

Basically I need J1 to show:
G1 if H1 and I1 are empty.
H1 plus 3 yrs if H1 filled.
I1 plus 1 yr, no matter if H1 filled or not.

Also, Is there a way I can shorten the code that it's showing in the Screen Shot? To do the same thing, add 3 yrs to a date in F1?

 

[NBVC]

How about:

=IF(I4<>"",DATEVALUE(MONTH(I4)&"/"&DAY(I4)&"/"&YEAR(I4)+1),IF(H4="",G4,(DATEVALUE(MONTH(H4)&"/"&DAY(H4)&"/"&YEAR(H4)+3))))

 

[JJRudd]

That's EXACTLY what I was looking for. Thanks a ton man.

 

[JJRudd]

This is just for my info, but can you explain what the <>"" does as apposed to just "" ? I'm just learning Excel and am trying to understand what all these formulas mean.

 

[jdee]

JJ:

I was working on a reply to you the other day re: another question - which someone replied to prior to my working it out. Then, later I was looking for your question, could not remember your name and your title was of no help for a search.
Now, I see you have an answer to this question - however, again the title of your question, "Here's a puzzling one for you guys to help me out with". would be of no help in a search.

Please do not take my comment wrong but a clear title to your question would be a great help - especially when trying a search, etc.

 

[NBVC]

This is just for my info, but can you explain what the <>"" does as apposed to just "" ? I'm just learning Excel and am trying to understand what all these formulas mean.

<> means does not equal to.

So <>"" means is not equal to null, therefore, the first IF statement checks if an entry is made in I and if so, it performs the first function, i.e. adds 1 year to that date, if the cell is blank then it will go to the second part of your function and so on....

 

[barry houdini]

Using EDATE function, available with Analysis ToolPak you can add 3 years to a date in A1 using

=EDATE(A1,36)

therefore your formula could be reduced to

=IF(I4<>"",EDATE(I4,12),IF(H4="",G4,EDATE(H4,36)))

 

[JJRudd]

So, now is there a way to automatically the date in I4 delete if H4 is greater than it? For instance, it was denied last year, then recieved this year. J4's formula would still be using I4 for it's date.

Sorry Jdee. I'll start being more specific with my subjects. I just did it this way to be a little more eye catching and be more likely to get an answer. I've noticed that 50 requests could be input in an hours time and mine would get lost in the jumble.

 

[barry houdini]

Perhaps you should change J4 to

=IF(I4>H4,EDATE(I4,12),IF(H4="",G4,EDATE(H4,36)))

 

[JJRudd]

That works really well, but is there any way that I can make it completely delete I4 if H4 is greater then I4?