CODEWITHSUNDEEP
google-sheets
fuzzie
fuzzie

Reputation: 5

Create Custom Quarter Grouping - March to May, June to August, etc

The following gives me appropriate Quarters: Jan to March, April to June, July to September:

YEAR('Sheet1'!A$2:A)&"-Q"&VLOOKUP(MONTH('Sheet1'!A$2:A),{1,1;4,2;7,3;10,4}

How would I customize this just to pull specifically: 3/1/21 - 5/31/21 or 3/1/21 - 5/31/21, 6/1/21 - 8/31/21, 9/1/21 - 11/31/21, etc.

Thanks a ton!

Upvotes: 0

Views: 40

Answers (1)

NightEye
NightEye

Reputation: 11194

Similar approach to what you've done so it would be easier to understand on your behalf.

Formula:

=ARRAYFORMULA(IF(NOT(ISBLANK(Sheet1!A$1:A)), 
VLOOKUP(MONTH(Sheet1!A$1:A),{1,"1/1","3/31";4,"4/1","6/30";7,"7/1","9/30";10,"10/1","12/31"} , 2, true )&"/"&TEXT(Sheet1!A$1:A, "YY")&" - "&
VLOOKUP(MONTH(Sheet1!A$1:A),{1,"1/1","3/31";4,"4/1","6/30";7,"7/1","9/30";10,"10/1","12/31"} , 3, true )&"/"&TEXT(Sheet1!A$1:A, "YY")
, ""))

Result:

output

Note:

  • It works, but it can be better.
  • This is for the normal quarter grouping, adjust the array inside the formula to do custom.

Custom quarters formula:

=join(" - ", arrayformula(to_date(index({
{12;SEQUENCE(3,1,3,3)}&"/1/"&{if(month(A1)=12,year(A1),year(A1)-1);sequence(3,1,year(A1),0)},
{if(month(A1)=12,eomonth(date(year(A1)+1,2,1), 0),eomonth(date(year(A1),2,1), 0)); eomonth(date(year(A1),5,1), 0); eomonth(date(year(A1),8,1), 0); eomonth(date(year(A1),11,1), 0)}
}, if(MOD(ROUNDUP((month(A1)+1)/3,0),5)=0, 1, MOD(ROUNDUP((month(A1)+1)/3,0), 5))))))

Output:

output

Upvotes: 1

Related Questions