# Calculating Quarters Within Dates

Options

Hello,

I'm trying to do an analysis that can calculate between a date range, specifically resulting in quarter calculations. If I have a project start and a project end date, how can I

a) calculate how many quarters are stretched between those 2 dates

b) if the date range (start date to end date) falls into the current quarter

So if start date is 12/18/2019 and end date is 11/23/20 then my results should look like this:

a) 5 (counting Q4-2019, Q1-2020, Q2-2020, Q3-2020 and Q4-2020)

b) Yes (we are currently in Q4-2020 so at least one of the above matches the current quarter)

Any idea how I can do this? I've been able to display the quarters for my dates via different formulas but can't seem to figure out how to do calculations with them...

• ✭✭✭✭✭
Options

Here's one way to do it. It's a bit complicated so I broke it down into pieces for you. I included the full combined formula in the last column.

=IF((DATE(YEAR([Start Date]@row), 12, 31) - [Start Date]@row) < (31 * 3), 1, IF((DATE(YEAR([Start Date]@row), 12, 31) - [Start Date]@row) < (31 * 6), 2, IF((DATE(YEAR([Start Date]@row), 12, 31) - [Start Date]@row) < (31 * 9), 3, 4))) + (ROUND((([End Date]@row - [Start Date]@row) / 365), 0) - 1) * 4 + IF(([End Date]@row - DATE(YEAR([End Date]@row), 1, 1)) < (31 * 3), 1, IF(([End Date]@row - DATE(YEAR([End Date]@row), 1, 1)) < (31 * 6), 2, IF(([End Date]@row - DATE(YEAR([End Date]@row), 1, 1)) < (31 * 9), 3, 4)))

I hope this helps.

Cheers,

Ramzi

Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

Feel free to email me: ramzi@cedartreeconsulting.com