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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

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?

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,051
Messages
5,835,135
Members
430,343
Latest member
Sailingexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back