Calculating Quarters Within Dates

Melisa Dannhauser

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

Thanks in advance!!

Ramzi K

@Melisa Dannhauser

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.

Combined formula for your convenience:

=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