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

JJRudd

New Member
Joined
Dec 6, 2005
Messages
34
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?
 

Some videos you may like

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
Joined
Aug 31, 2005
Messages
5,828
How about:

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

JJRudd

New Member
Joined
Dec 6, 2005
Messages
34
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
Joined
Sep 10, 2003
Messages
276

ADVERTISEMENT

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
Joined
Aug 31, 2005
Messages
5,828
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
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

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
Joined
Dec 6, 2005
Messages
34
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
Joined
Mar 23, 2005
Messages
20,825
Perhaps you should change J4 to

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

JJRudd

New Member
Joined
Dec 6, 2005
Messages
34
That works really well, but is there any way that I can make it completely delete I4 if H4 is greater then I4?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,282
Messages
5,571,301
Members
412,379
Latest member
achugg22
Top