Calculating Quarters with a Start and a Finish date

TJ North

I searched the community and did not see a formula that calculated Quarter based on a Start and a Finish date while also accounting for cases where the year for the Start and Finish date differ. The formula below handles that. A few notes …

1. It assumes that Finish is always >= Start. The entire formula is wrapped with IFERROR, so if you return a blank value it likely means that either (a) one of the dates is blank, or (b) Finish is before Start.
2. The first section handles all cases where the Start and Finish are in the same year,
3. The second section handles all cases where Finish is 1 year ahead of Start,
4. The third section handles all cases where Finish is more than 1 year ahead of Start (i.e., where the activity is happening in all 4 quarters)

Replace "Start" and "Finish" in the formula below with the names of your start and finish dates. Enjoy!

=IFERROR(

IF(AND(YEAR(Finish@row ) = YEAR(Start@row ), ROUNDUP(MONTH(Finish@row ) / 3) = ROUNDUP(MONTH(Start@row ) / 3)), "Q" + ROUNDUP(MONTH(Start@row ) / 3),
IF(AND(YEAR(Finish@row ) = YEAR(Start@row ), ROUNDUP(MONTH(Finish@row ) / 3) - ROUNDUP(MONTH(Start@row ) / 3) = 1), JOIN(DISTINCT("Q" + ROUNDUP(MONTH(Start@row ) / 3), "Q" + ROUNDUP(MONTH(Finish@row ) / 3)), CHAR(10)),
IF(AND(YEAR(Finish@row ) = YEAR(Start@row ), ROUNDUP(MONTH(Finish@row ) / 3) - ROUNDUP(MONTH(Start@row ) / 3) = 2), JOIN(DISTINCT("Q" + ROUNDUP(MONTH(Start@row ) / 3), "Q" + ROUNDUP((MONTH(Start@row )+3) / 3),"Q" + ROUNDUP(MONTH(Finish@row ) / 3)), CHAR(10)),
IF(AND(YEAR(Finish@row ) = YEAR(Start@row ), ROUNDUP(MONTH(Finish@row ) / 3) - ROUNDUP(MONTH(Start@row ) / 3) = 3), ("Q1" + CHAR(10) + "Q2" + CHAR(10) + "Q3" + CHAR(10) + "Q4"),

IF(AND(YEAR(Finish@row ) - YEAR(Start@row ) = 1, ROUNDUP((MONTH(Finish@row )) / 3) + 4 - ROUNDUP(MONTH(Start@row ) / 3) = 1), JOIN(DISTINCT("Q" + ROUNDUP(MONTH(Start@row ) / 3), "Q" + ROUNDUP(MONTH(Finish@row ) / 3)), CHAR(10)),
IF(AND(YEAR(Finish@row ) - YEAR(Start@row ) = 1, ROUNDUP((MONTH(Finish@row )) / 3) + 4 - ROUNDUP(MONTH(Start@row ) / 3) = 2, ROUNDUP(MONTH(Start@row ) / 3) = 3), ("Q1" + CHAR(10) + "Q3" + CHAR(10) + "Q4"),
IF(AND(YEAR(Finish@row ) - YEAR(Start@row ) = 1, ROUNDUP((MONTH(Finish@row )) / 3) + 4 - ROUNDUP(MONTH(Start@row ) / 3) = 2, ROUNDUP(MONTH(Start@row ) / 3) = 4), ("Q1" + CHAR(10) + "Q2" + CHAR(10) + "Q4"),
IF(AND(YEAR(Finish@row ) - YEAR(Start@row ) = 1, ROUNDUP((MONTH(Finish@row )) / 3) + 4 - ROUNDUP(MONTH(Start@row ) / 3) = 3), ("Q1" + CHAR(10) + "Q2" + CHAR(10) + "Q3" + CHAR(10) + "Q4"),

IF(YEAR(Finish@row ) - YEAR(Start@row ) > 1, ("Q1" + CHAR(10) + "Q2" + CHAR(10) + "Q3" + CHAR(10) + "Q4"),""))))))))),"")

Kamal0625

Instead of many IFs, compute a single quarter span length, this gives you how many quarters are covered between Start and Finish, no matter how many years apart.

=IFERROR(IF(OR(ISBLANK(Start@row ), ISBLANK(Finish@row ), Finish@row < Start@row ), "", IF((4*(YEAR(Finish@row )-YEAR(Start@row )) + ROUNDUP(MONTH(Finish@row )/3) - ROUNDUP(MONTH(Start@row )/3) + 1) >= 4, JOIN(DISTINCT("Q" + ROUNDUP(MONTH(Start@row )/3), "Q" + MOD(ROUNDUP(MONTH(Start@row )/3),4)+1, "Q" + MOD(ROUNDUP(MONTH(Start@row )/3)+1,4)+1, "Q" + MOD(ROUNDUP(MONTH(Start@row )/3)+2,4)+1), CHAR(10)), IF((4*(YEAR(Finish@row )-YEAR(Start@row )) + ROUNDUP(MONTH(Finish@row )/3) - ROUNDUP(MONTH(Start@row )/3) + 1) = 3, JOIN(DISTINCT("Q" + ROUNDUP(MONTH(Start@row )/3), "Q" + MOD(ROUNDUP(MONTH(Start@row )/3),4)+1, "Q" + MOD(ROUNDUP(MONTH(Start@row )/3)+1,4)+1), CHAR(10)), IF((4*(YEAR(Finish@row )-YEAR(Start@row )) + ROUNDUP(MONTH(Finish@row )/3) - ROUNDUP(MONTH(Start@row )/3) + 1) = 2, JOIN(DISTINCT("Q" + ROUNDUP(MONTH(Start@row )/3), "Q" + MOD(ROUNDUP(MONTH(Start@row )/3),4)+1), CHAR(10)), "Q" + ROUNDUP(MONTH(Start@row )/3)))))),"")