Semi-annual issues

Hi everybody,

I'm new here and I got a little question for all Excel-wizards out there. I´ve been trying to come up with equation which would round some dates to the end of the next semi-annual date for that or the next year. I found this

"Rounding to Semi-Annual Dates
Hello All,
Should be a quick one for you guru's. I need to round some dates to the nearest semi-annual date for that year.
Ex. - 4/31/2007 = 6/30/2007.
Ex. - 9/30/2007 = 12/31/2007.

There are dates from 2006 and 2005 as well. I was trying to find something similar to '=EOMONTH' for semi-annual dates, but to no avail.

Thanks!

Join Date: 26 Jul 2007

Ok, if A1 is the Date then where this line is will do that

=IF(MONTH(A1)>6,DATE(YEAR(A1),12,31),DATE(YEAR(A1),6,30))

Explanation:

Month(), Day(), Year() takes a date and takes the number for day, month or year out. and the Date() puts it all back together


from this site and it´s a good start but not working for me. This is what I mean:

ex: 14-Jun-2010 = 31-Mar-2011
ex: 20-Oct-2010 = 31-Sep-2011

I´ve been banging my head against the wall for quite some time and would appreciate any help!

EDIT:

Sorry guys I wasn't specific enough, my bad.

So this is what need the formula to do:

Input: 14-Jun-2010, Output should be: 31-Mar-2011
Input: 20-Oct-2010, Output should be: 31-Sep-2011

This is question concerning a semi-annual training event, as if somebody accomplishes an event in i.e 14-Jun-2010 he´s due on the 31-Mar-2011, so he´s good until the end of the next semi-annual period.
Anyone?
EDIT: Thanks KIPA for pointing this one out!

This returns the 3/31 or 10/31 following the date:

=EOMONTH(A1, LOOKUP(MONTH(A1), {1,4,11}, {3,10,15}) - MONTH(A1))

E.g.,

Please Login or Register  to view this content.

Not sure why it's giving you a problem; seems OK to me.

Excel 2003, Windows XP:

HTML Code: 

Regards

You might want to fix the first post edit, since it has a different output of 20-Oct-2010. This of course assuming that the due date is 31-Sep but not 31-Oct.

Source data in B2:

Please Login or Register  to view this content.

It's a bit of a mess, might deduct (more).

=eomonth(a1, lookup(month(a1), {1,4,10}, {9,15,21}) - month(a1))

Try this formula

=DATE(YEAR(A1),CEILING(MONTH(A1)+3,6)+4,0)

or using EOMONTH....

=EOMONTH(A1,MOD(3-MONTH(A1),6)+6)