Calculating Quarters Within Dates

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

Answers

  • Ramzi K
    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

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

    Feel free to email me: [email protected]

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!