Automatic calculation of Quarter to Date data based on Month

[tsunami1977]

Here is one more problem that I am facing with this file.

I have all the 2004 and 2005 data by month.

In the summary page, the person using the data is suppose to enter 2005 monthly actual as the data comes in.

I now have the 2005 and 2004 monthly comparison working. But I also need to do a Quarter To Date comparison for 2005 and 2004.

How can I create a formula that can calculate Quarter to Date information if the Date criteria is 1-1-05 and so forth?

For example, if the date is 2-1-05, how can I get Excel to automatically calculate QTD data for Q1'05 which will be Jan'05 + Feb'05. If date is 5-1-05, the QTD data will be for Q2'05 (April'05 + May'05).

I have thought of using pivot table for this, but I actually have 2005 actual data, 2004 actual, and also 2005 plan. Plus I have to do a Year to Date comparison also.

Any help would be appreciated.

Thank you!!

 

[tactps]

In a cell (B1), type this:
=DATE(YEAR(A1),CEILING(MONTH(A1),3)-2,1)
which will give you the first date of the quarter.

In another cell (c1):
=DATE(YEAR(A1),CEILING(MONTH(A1),3)+1,0)
which is the last date of the quarter.

Then use a sumif:
=sumif(range_of_dates,">=B1",range_to_sum)-=sumif(range_of_dates,"<=C1",range_to_sum)

EDIT: Sumif should be:
=sumif(range_of_dates,">=B1",range_to_sum)-=sumif(range_of_dates,">C1",range_to_sum)