Finding a date range from given date

[Sarah (OGI)]

How can I write as an expression in excel that:

identifies the full 12 months prior to a given date
AND
identifies the full 12 months after a given date

So for a given date of 30/03/2006, how can I say what the date range is 12
months prior - the result I want is March 2005 - Feb 2006 (28/03/2005 -
28/02/2006).

The 12 month range after the above date needs to be April 2006 - March 2007
(28/04/2006 - 28/03/2007).

I want to be able to do a sumif, to find the given date in A2 and sum all
those cells that fall into the required date range, i.e. the previous 12
months.

Thanks in advance.

 

[Bob Phillips]

This checks if a date is in the full year prior to a date in D1

=IF(AND(myDate>=DATE(YEAR(D1)-1,MONTH(D1),1),myDate<=DATE(YEAR(D1),MONTH(D1),0)),"Yes","No")

and this the next full year

=IF(AND(myDate>=DATE(YEAR(D1),MONTH(D1)+1,1),myDate<=DATE(YEAR(D1)+1,MONTH(D1)+1,0)),"Yes","No")

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)