Thread: Calculate Date Six Months from Cell Date, ongoing

Hello!

I have a start date for a contract in cell AM1. I need AN1 show a date six months from the start date in AM1. I am fine with that part.

However, I would like for AN1 to calculate not only a six-month date but also to auto-update to the next six month date from AM1 once the first six month date is about, let's say, one month past. What I am doing is calculating when a six-month inspection needs to be completed; these inspections are ongoing, so I need them to auto-update.

So let's say a contract was awarded today, 25 JAN 2010. That would be value in AM1.

I want AN1 to produce a date six months from 25 JAN 2010, which would be 25 JUL 2010 (yes, this way of calculating the six month date is fine). Then around, let's say, 25 AUG 2010, I want AN1 to auto-update to produce the next six month date, which would be 25 JAN 2011 (six months from 25 JUL). Obviously the function would need to relate to TODAY() in some way.

Is this possible?

Nope, didn't work. Any other ideas? :o)

I think JB has the right idea but, assuming you want the date to show as 6 months in the future right now try like this

=DATE(YEAR(AM1),MONTH(AM1)+MAX(6,CEILING(DATEDIF(AM1,A1,"m"),6)),DAY(AM1))

where A1 has today's date. Always updates to the next date 1 month after the previous. You can shorten if you use EDATE, i.e.

=EDATE(AM1,MAX(6,CEILING(DATEDIF(AM1,W1,"m"),6)))

Whoops! Sorry! Maybe I have my "date" column formatted incorrectly? Because when I enter in the formulas above, I get #VALUE!

Since my original spreadsheet has some sensitive information, I am creating a verrrrrry basic version here, where instead of Column AM with the dates, Column B will have the dates. Attached is what I am getting.

Perfect! Thanks so much!

What you provided definitely works if the date in Column B is in the first half of the year (in other words, if you add six months to the date, the resulting date will still be in the same year).

However, if the date is after 6.30 (therefore six months added would result in a date in the following year), the formula doesn't compute. Also, for dates more than six months before TODAY(), the formula doesn't work either.

... For instance, in my sample table, attached, I have 6.15.09 in B5. C5 should produce 5.15.10 (since six months added to 6.15.09 is 12.15.09, and since that date has passed, another six months from that date would be 5.15.10). But instead it produces 12.15.2018.

Any more suggestions?

Perfect! Thank you so much!!!