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!
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

sborj

New Member
Joined
Feb 23, 2016
Messages
5
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!!!
 

Noodleski

Active Member
Joined
Nov 18, 2015
Messages
467

ADVERTISEMENT

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?
 

sborj

New Member
Joined
Feb 23, 2016
Messages
5
Yes that works great.

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

Noodleski

Active Member
Joined
Nov 18, 2015
Messages
467

ADVERTISEMENT

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.
 

sborj

New Member
Joined
Feb 23, 2016
Messages
5
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.
 

Noodleski

Active Member
Joined
Nov 18, 2015
Messages
467
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,270
Messages
5,600,638
Members
414,398
Latest member
dhune

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