Formula for Number of Quarters

[sborj]

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!

 

[Noodleski]

roundup((month(enddate)-month(startdate))/3,0)

 

[sborj]

Awesome! Works perfectly!

 

[sborj]

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]

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]

Yes that works great.

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

 

[Noodleski]

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]

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]

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)