Formula for Number of Quarters

sborj

New Member
Joined
Feb 23, 2016
Messages
5
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.
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!!!
 
Upvote 0
Well, you could always add
(year(enddate)-year(startdate))*4 to the formula you already have.

Jumping from jan 2016 to may 2017 gives you 5 then.
Would that help you?
 
Upvote 0
Yes that works great.

last question. Would you happen to know the formula for Semi-Annual?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
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.
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
Back
Top