# Automatic calculation of Quarter to Date data based on Month

#### tsunami1977

##### Board Regular
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!!

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### tactps

##### Well-known Member
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)

Replies
6
Views
135
Replies
3
Views
149
Replies
5
Views
98
Replies
1
Views
166
Replies
1
Views
185

1,181,614
Messages
5,930,946
Members
436,767
Latest member
Langaws

### 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?

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