# Formula for Number of Quarters

#### sborj

##### New Member
Hello,

I need some help on a formula. I need to calculate the number of Quarters between two dates.

Details:
Q1: 1/1-3/31
Q2: 4/1-6/30
Q3: 7/1-9/31
Q4: 10/1-12/31

Examples and Conditions:
Start Date=1/1 and End Date=12/31. The Number of Quarters should be 4.
Start Date=4/1 and End Date=12/31. The Number of Quarters should be 3.
Start Date=1/1 and End Date=6/30. The Number of Quarters should be 2.
Start Date=4/1 and End Date=9/31. The Number of Quarters should be 2.

Start Date=2/1 and End Date=6/30. The Number of Quarters should be 1. The Start Date passed the beginning of Quarter 1 so it should no longer be counted.

Start Date=2/1 and End Date=8/30. The Number of Quarters should be 1. The Start Date passed the beginning of Quarter 1 so it should no longer be counted and the End Date did not reach the end of Q3.

Start Date=3/1 and End Date=11/30. The Number of Quarters should be 2. The Start Date passed the beginning of Quarter 1 so it should no longer be counted and the End Date did not reach the end of Q4.

Hope this makes sense. If not please let me know if there are more clarifications needed. Would really appreciate some help!

Thank you!

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
roundup((month(enddate)-month(startdate))/3,0)

Awesome! Works perfectly!

Hello Noodleski,

Sorry my response is a bit late but do you have a formula that takes the year into consideration as well?

Thanks so much!!!

(year(enddate)-year(startdate))*4 to the formula you already have.

Jumping from jan 2016 to may 2017 gives you 5 then.

Yes that works great.

last question. Would you happen to know the formula for Semi-Annual?

Yes that works great.

last question. Would you happen to know the formula for Semi-Annual?
You mean count the number of half years in between two dates?
If we take this base formula for quarters:
roundup((month(enddate)-month(startdate))/3,0)
The division by three is because there are max 4 quarters in a year.
So if we need this for semesters, change it to 6 instead of 3.

If we take this base formula for quarters:
roundup((month(enddate)-month(startdate))/3,0)
The division by three is because there are max 4 quarters in a year.
So if we need this for semesters, change it to 6 instead of 3.

Yes I tried this but when the StartDate is from 2/1/16-5/1/16 and EndDate 12/31/2016 it returns 2. It should be 1.

Also the year need to be taken into consideration.

Hm, didn't realise it at first, but it will probably be different as 3 is odd and 6 is even.
Try this:

(YEAR(enddate)-YEAR(startdate))*2+ROUNDDOWN((MONTH(enddate)-MONTH(startdate))/6,0)

Replies
5
Views
155
Replies
3
Views
222
Replies
5
Views
318
Replies
4
Views
441
Replies
0
Views
371

1,217,758
Messages
6,138,439
Members
450,137
Latest member
HANHAN

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

### Which adblocker are you using?

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

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