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

#### JJRudd

##### New Member
Practice Dec Tracker.xls
GHIJ
3First Date EligibleLast Date ReceivedLast Date DeniedNext Date Eligible
422-Feb-9727-Mar-0527-Mar-08
58-Jul-9923-Jun-0223-Jun-05
623-Oct-0123-Oct-01
723-Mar-0323-Mar-03
824-Apr-0424-Apr-04
924-Nov-0424-Nov-04
107-Jan-057-Jan-05
1113-Jan-0527-Sep-0427-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?

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### NBVC

##### Well-known Member

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

#### JJRudd

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

#### JJRudd

##### New Member
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

##### Active Member

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

##### Well-known Member
JJRudd said:
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

##### MrExcel MVP

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

##### New Member
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

##### MrExcel MVP
Perhaps you should change J4 to

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

#### JJRudd

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

Replies
10
Views
103
Replies
3
Views
94
Replies
2
Views
46
Replies
1
Views
186
Replies
3
Views
177