Finding a date range from given date
 

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.

Finding a date range from given date
 

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),myDat e<=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)



"Sarah (OGI)" wrote in message
...
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.