Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
Could I please obtain some help creating an Excel IF statement, considering the following parameters:
- There is a date in cell A1
- The statement will appear in A2
- If the date in A1 is more than 30 days prior to Oct 15, add 30 days to the date in A1
- If the date in A1 is less than 30 days prior to Oct 15, then May 15 of the following year should appear with 30 days added to it, less the amount of days that the date in A1 is before Oct 15 (i.e. Oct 13, 2005 in A1 would mean that A2 produces June 12, 2006 or Oct 15 - Oct 13 = 2 and May 15 + 30 - 2 = June 12)
- If the date in A1 is past Oct 15, regardless of the amount of days, then 30 days should be added on to May 15 of the following yearIs that possible to do? Does my explanation make any sense? Thanks.
Yes it's possible, after you answer these 3 questions:
1 - Your criteria are either "less than 30 days prior" or "more than 30 days prior". What happens if A1 is exactly 30 days prior?
2 - Why do you give us criteria such as "less than 30 days prior to Oct 15"?
Isn't your criteria more simply stated as:
Prior to Sept 15
Between Sept 15 and Oct 15
After Oct 15(We still need to know what the result should be for exactly Sept 15 and October 15)
3 - Why tell us to add 30 to May 15 instead of simply saying the result should be June 14?
All the extra math is making me think I'm missing something.
0 
1. If A1 is exactly 30 days prior, then Oct 15 should show up in A2. Which means that if A1 is Oct 15, then June 14 should show up in A2.
2. Yes, you stated the criteria in a simpler manner. Sorry if I made it seem more complicated.
3. Well, the result would be June 14 only when the date is past Oct 15. If the date in A1 is between Sept 15 and Oct 15, then 30 needs to be added to May 15, less the amount of days that the date (in A1) is prior to Oct 15.
I don't think you are missing anything. Perhaps the actual reasoning for my query makes some sense: I am trying to set up a sheet for inspections of 30-60-90 days after an initial installation date and from Oct 15 to May 15 is a dormant period where no inspections should occur.
0
You've contradicted yourself in your criteria.
1. ... Which means that if A1 is Oct 15, then June 14 should show up in A2
3. Well, the result would be June 14 only when the date is past Oct 15
Which is it: June 14 when A1 *is* Oct 15 or when A1 is *past* Oct 15.
In any case, I believe the format you need to use is a Nested IF with the DATEVALUE function.
Something like this seems to work, although it may need to be adjusted once you clarify once you clarify the relationship between Oct 15 and June 14.
=IF(A1<=DATEVALUE("September 15"),A1+30,IF(AND(A1>DATEVALUE("September 15"),A1<=DATEVALUE("October 15")),365+DATEVALUE("June 14")-(DATEVALUE("October 15")-A1),"June 14, " & YEAR(TODAY())+1))
0
OK, if A1 is Oct 15, 2005 then June 14, 2006 should be the result in A2, but if the result in A2 is Oct 15, 2005 then it should stay Oct 15, 2005. Does that make any sense?
0
Not sure if this response got through...my broswer through up an error. Trying again..
Let's clarify something else:
You keep using 2005 and 2006 in your examples. Are you really concerned with those years or are you dealing with "real time" as in it's now 2008, next year is 2009, etc?
If you are asking about years other than the current year (at any given time) then you have added an additional layer of complexity to the problem.
Can I assume that the dates in A1 are always given as current year dates?
0
Hello DerbyDad03,
I asked a similar question a few months ago and you came up with this formula:
=IF(F7+30>DATEVALUE("10/15/"&YEAR(F7)),DATEVALUE("5/15/"&YEAR(F7)+1)+(F7+30)-DATEVALUE("10/15/"&YEAR(F7)),F7+30)
which worked for all years. The thing was I asked for the amount of days past Oct 15 to be added on to May 15. Is there a way to revise the above statement to not add the amount of days past Oct 15 on to May 15 and just add 30?
0
I thought your question looked very familiar, however, you've now got my head spinning with all these different requests.
Does the formula I suggested a few months ago meet the criteria of the first post in *this* thread? If so, why did you ask for it again? If not, what's different?
Now, in your last response, it looks like you changed your criteria by not caring about the days past Oct 15.
Are we solving 2 different problems here?
0
The formula doesn't meet the criteria of the first post in *this* thread because the number of days that a date may be past Oct 15 is always added on to the 30 + May 15. So if say A1 is Oct 18 then A2 is June 17 when I just want it to be June 14.
My last response was indicating what I asked for the first time (when I did care about the number of days past Oct 15, I now don't).
We (more specifically, you, as I just seem to be adding confusion) aren't solving 2 different problems as you solved the first problem that I had a few months ago. The thing is, when I was entering data into the sheet with your first formula, I noticed it was giving me what I did ask for but not what I should have asked for.
Conclusion. The difference between my original problem and the one I have now is that I don't want the formula to add the number of days past Oct 15 on to the 30 + May 15. So I would like any date from Oct 16 to May 14 in A1 would produce June 14 in A2.
If you can't help that is fine, I use a calendar. :)
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
