Problem with calculating date 6 months into the future

[Torfinn Brokke]

Hello!

I'm trying to get a formula to work which will allow me to calculate a date
half a year into the future. I'm using MONTH(D27)+6, where D21 contains a
date. But if I input, say 31.08.2008 here, I get the output 03.03.2009,
instead of 28.02.2009, which is what I'm looking for (the date format I'm
using is DD.MM.YYYY, by the way).

I also tried with YEAR(D27)+0,5, but that doesn't seem to work at all.
YEAR(D27)+1, on the other hand, seems to work perfectly if the duration is
one year.

What do I need to do to make it correct for half a year?


Best regards,
Torfinn

 

[David Biddulph]

Well, the first thing you need to do is to define *your* rules for what you
think is half a year ahead.

One formula that might deal with your specific example is
=MIN(DATE(YEAR(D27),MONTH(D27)+6,DAY(D27)),DATE(YEAR(D27),MONTH(D27)+7,0))
but *you* need to decide what *you* want.

 

[Torfinn Brokke]

Thank you! With some slight adaptations, that did the trick!


Best regards,
Torfinn

 

[Ashish Mathur]

Hi,

You may use the EDATE() function

=edate(D21,6)

If you are using Excel 2003/prior versions, then you must install the
Anaysis Toolpak

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com