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 …
- 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.
- The first section handles all cases where the Start and Finish are in the same year,
- The second section handles all cases where Finish is 1 year ahead of Start,
- 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"),""))))))))),"")
