Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
I have a date in cell (f7) and want to add 30 days onto it in cell (i7) but, if the date in cell (i7) turns out to be greater than Oct 15 I want to subtract the amount of days more than Oct 15 it is and add that amount to May 15. Can anyone help?
So, let's say your date in cell (i7) is Oct 17 then you want to subtract two days and add it to May 15 making May 17?
Where would May 17 appear; in (i7)?
Bryan
0 
I think this works. Let me know...
I put September 17, 2008 in F7 and got May 17, 2008 in I7.
I put October 17, 2008 in F7 and got June 16, 2008 in I7.
=IF(F7+30>DATEVALUE("10/15/2008"),DATEVALUE("5/15/2008")+((F7+30)-DATEVALUE("10/15/2008")),F7+30)
0
Hmmm...just realized that you didn't say anything about what year (or years) we are talking about, so my formula is hardcoded for 2008 dates. This should work for every year, and assumes the May 15 we are talking about is in the year after the Oct 15.
=IF(F7+30>DATEVALUE("10/15/"&YEAR(NOW())),DATEVALUE("5/15/"&YEAR(NOW())+1)+(F7+30)-DATEVALUE("10/15/"&YEAR(NOW())),F7+30)
0
Okay, I put Oct 2, 2005 into cell (F7) and copied your second statement into cell (I7) and got Nov 1, 2005. When I put Oct 2, 2008 into cell (F7) I get June 1, 2009 in cell (I7) so I don't think the second statement works for all years.
Also, I added some more information into the spreadsheet and copied the statement into another cell (L7), changed the references to from cell (F7 to I7) and ended up with Jan 29, 2010 in cell (L7).
0
DerbyDad03,
I was hoping you would chime in after getting clarification on which cell was the target cell.
Thanks and regards,
Bryan
0
Well, when I said "every year" I meant "starting now and going forward". I didn't realize you were living in the past. <g>
How *do* you want it to work? Will you be working with previous years (e.g. 2005)? If so, what May 15 are we basing the results on? May 15 of the same year or the next year or ...?
0
I am sorry for being vague. I am working with previous years (2005, 2006, 2007). The May 15 would be the following year (eg. Oct 15, 2005 and May 15, 2006.)
0
No need to apologize...if you didn't know that the years you planned on using mattered in the make up of the formula, then you wouldn't have known to tell us about them.
How about this...
This version uses the year of the value in F7 to determine what year to use in the DATEVALUE functions.
=IF(F7+30>DATEVALUE("10/15/"&YEAR(F7)),DATEVALUE("5/15/"&YEAR(F7)+1)+(F7+30)-DATEVALUE("10/15/"&YEAR(F7)),F7+30)
If this works for you, then I should of thought of using the year of the original date in the first place. See what happens when I rush things? <g>
0
I appreciate the help very much. The statement works for all of the years that I need it to. Thanks again!
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
