Automatic calculation of Quarter to Date data based on Month

tsunami1977

Board Regular
Joined
Jan 24, 2005
Messages
64
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!! :rolleyes:
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
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)
 

Forum statistics

Threads
1,147,696
Messages
5,742,679
Members
423,747
Latest member
Shadeslayers09

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top