I have an Excel spreadsheet that includes weekly donations by contributors across weeks (Sunday). The weekly data is summed monthly, quarterly, and yearly. Is there a way that I can set up a function tied to C1 such that when I enter in a new year, the weeks (Sunday) expand or contract based on the number of Sundays in each month? Thanks.
After 24 years as an IT consultant, I'm going to go out on a limb here.
This is a classic case of "just because we CAN do something does not mean we SHOULD do it." Setting up this spreadsheet will be done once a year, and can't take very long manually. Those of us who have made suggestions (yes, that includes me!) have already spent more time than the requestor could possibly save doing it manually for the next several decades!
With that in mind, I've removed ANY suggestion of creating a macro from my reply.
Create a master spreadsheet that has the maximum number of columns per month that can occur in each month (5). When you set up a new year, hide columns that aren't needed for that year. Just hide them manually since it's only once a year. By hiding columns rather than deleting them, you won't risk breaking the monthly/quarterly subtotal formulas.
The column headers are simple, enter the 1st Sunday of the year in C1 as in the question. In D1 enter the formula =C1+7, and copy D1 across columns. Those Row 1 Sunday formulas will obviously have to skip over the subtotal columns.
All references to macros or other form of automation! :)
You can use this formula in cell D1:
=IF(C1="","",IF(ISNUMBER(C1),IF(MONTH(C1+7)<>MONTH(C1),UPPER(TEXT(C1,"mmm"))&" TOT",C1+7),IF(LEFT(C1,3)="QTR",IF(LEFT(C1,4)="QTR4",YEAR(A3)&" TOT",IF(YEAR(A3+7)<>YEAR(A3),"",A3+7)),IF(LEFT(B1,4)="QTR4","",IF(MOD(MONTH(A3),3)=0,"QTR"&MONTH(A3)/3&" TOT",IF(YEAR(B1+7)<>YEAR(B1),"",B1+7))))))
In case you need it, you can also use this formula in cell C1 (assuming you specify the year in cell B1):
=DATE(B1,1,1)+7-WEEKDAY(DATE(B1,1,1),11)
Here the example extended:
| Name | 2023 | =DATE(B1,1,1)+7-WEEKDAY(DATE(B1,1,1),11) | =IF(C1="","",IF(ISNUMBER(C1),IF(MONTH(C1+7)<>MONTH(C1),UPPER(TEXT(C1,"mmm"))&" TOT",C1+7),IF(LEFT(C1,3)="QTR",IF(LEFT(C1,4)="QTR4",YEAR(A3)&" TOT",IF(YEAR(A3+7)<>YEAR(A3),"",A3+7)),IF(LEFT(B1,4)="QTR4","",IF(MOD(MONTH(A3),3)=0,"QTR"&MONTH(A3)/3&" TOT",IF(YEAR(B1+7)<>YEAR(B1),"",B1+7)))))) | =IF(D1="","",IF(ISNUMBER(D1),IF(MONTH(D1+7)<>MONTH(D1),UPPER(TEXT(D1,"mmm"))&" TOT",D1+7),IF(LEFT(D1,3)="QTR",IF(LEFT(D1,4)="QTR4",YEAR(B1)&" TOT",IF(YEAR(B1+7)<>YEAR(B1),"",B1+7)),IF(LEFT(C1,4)="QTR4","",IF(MOD(MONTH(B1),3)=0,"QTR"&MONTH(B1)/3&" TOT",IF(YEAR(C1+7)<>YEAR(C1),"",C1+7)))))) | =IF(E1="","",IF(ISNUMBER(E1),IF(MONTH(E1+7)<>MONTH(E1),UPPER(TEXT(E1,"mmm"))&" TOT",E1+7),IF(LEFT(E1,3)="QTR",IF(LEFT(E1,4)="QTR4",YEAR(C1)&" TOT",IF(YEAR(C1+7)<>YEAR(C1),"",C1+7)),IF(LEFT(D1,4)="QTR4","",IF(MOD(MONTH(C1),3)=0,"QTR"&MONTH(C1)/3&" TOT",IF(YEAR(D1+7)<>YEAR(D1),"",D1+7)))))) | =IF(F1="","",IF(ISNUMBER(F1),IF(MONTH(F1+7)<>MONTH(F1),UPPER(TEXT(F1,"mmm"))&" TOT",F1+7),IF(LEFT(F1,3)="QTR",IF(LEFT(F1,4)="QTR4",YEAR(D1)&" TOT",IF(YEAR(D1+7)<>YEAR(D1),"",D1+7)),IF(LEFT(E1,4)="QTR4","",IF(MOD(MONTH(D1),3)=0,"QTR"&MONTH(D1)/3&" TOT",IF(YEAR(E1+7)<>YEAR(E1),"",E1+7)))))) | =IF(G1="","",IF(ISNUMBER(G1),IF(MONTH(G1+7)<>MONTH(G1),UPPER(TEXT(G1,"mmm"))&" TOT",G1+7),IF(LEFT(G1,3)="QTR",IF(LEFT(G1,4)="QTR4",YEAR(E1)&" TOT",IF(YEAR(E1+7)<>YEAR(E1),"",E1+7)),IF(LEFT(F1,4)="QTR4","",IF(MOD(MONTH(E1),3)=0,"QTR"&MONTH(E1)/3&" TOT",IF(YEAR(F1+7)<>YEAR(F1),"",F1+7)))))) | =IF(H1="","",IF(ISNUMBER(H1),IF(MONTH(H1+7)<>MONTH(H1),UPPER(TEXT(H1,"mmm"))&" TOT",H1+7),IF(LEFT(H1,3)="QTR",IF(LEFT(H1,4)="QTR4",YEAR(F1)&" TOT",IF(YEAR(F1+7)<>YEAR(F1),"",F1+7)),IF(LEFT(G1,4)="QTR4","",IF(MOD(MONTH(F1),3)=0,"QTR"&MONTH(F1)/3&" TOT",IF(YEAR(G1+7)<>YEAR(G1),"",G1+7)))))) | =IF(I1="","",IF(ISNUMBER(I1),IF(MONTH(I1+7)<>MONTH(I1),UPPER(TEXT(I1,"mmm"))&" TOT",I1+7),IF(LEFT(I1,3)="QTR",IF(LEFT(I1,4)="QTR4",YEAR(G1)&" TOT",IF(YEAR(G1+7)<>YEAR(G1),"",G1+7)),IF(LEFT(H1,4)="QTR4","",IF(MOD(MONTH(G1),3)=0,"QTR"&MONTH(G1)/3&" TOT",IF(YEAR(H1+7)<>YEAR(H1),"",H1+7)))))) | =IF(J1="","",IF(ISNUMBER(J1),IF(MONTH(J1+7)<>MONTH(J1),UPPER(TEXT(J1,"mmm"))&" TOT",J1+7),IF(LEFT(J1,3)="QTR",IF(LEFT(J1,4)="QTR4",YEAR(H1)&" TOT",IF(YEAR(H1+7)<>YEAR(H1),"",H1+7)),IF(LEFT(I1,4)="QTR4","",IF(MOD(MONTH(H1),3)=0,"QTR"&MONTH(H1)/3&" TOT",IF(YEAR(I1+7)<>YEAR(I1),"",I1+7)))))) | =IF(K1="","",IF(ISNUMBER(K1),IF(MONTH(K1+7)<>MONTH(K1),UPPER(TEXT(K1,"mmm"))&" TOT",K1+7),IF(LEFT(K1,3)="QTR",IF(LEFT(K1,4)="QTR4",YEAR(I1)&" TOT",IF(YEAR(I1+7)<>YEAR(I1),"",I1+7)),IF(LEFT(J1,4)="QTR4","",IF(MOD(MONTH(I1),3)=0,"QTR"&MONTH(I1)/3&" TOT",IF(YEAR(J1+7)<>YEAR(J1),"",J1+7)))))) | =IF(L1="","",IF(ISNUMBER(L1),IF(MONTH(L1+7)<>MONTH(L1),UPPER(TEXT(L1,"mmm"))&" TOT",L1+7),IF(LEFT(L1,3)="QTR",IF(LEFT(L1,4)="QTR4",YEAR(J1)&" TOT",IF(YEAR(J1+7)<>YEAR(J1),"",J1+7)),IF(LEFT(K1,4)="QTR4","",IF(MOD(MONTH(J1),3)=0,"QTR"&MONTH(J1)/3&" TOT",IF(YEAR(K1+7)<>YEAR(K1),"",K1+7)))))) | =IF(M1="","",IF(ISNUMBER(M1),IF(MONTH(M1+7)<>MONTH(M1),UPPER(TEXT(M1,"mmm"))&" TOT",M1+7),IF(LEFT(M1,3)="QTR",IF(LEFT(M1,4)="QTR4",YEAR(K1)&" TOT",IF(YEAR(K1+7)<>YEAR(K1),"",K1+7)),IF(LEFT(L1,4)="QTR4","",IF(MOD(MONTH(K1),3)=0,"QTR"&MONTH(K1)/3&" TOT",IF(YEAR(L1+7)<>YEAR(L1),"",L1+7)))))) | =IF(N1="","",IF(ISNUMBER(N1),IF(MONTH(N1+7)<>MONTH(N1),UPPER(TEXT(N1,"mmm"))&" TOT",N1+7),IF(LEFT(N1,3)="QTR",IF(LEFT(N1,4)="QTR4",YEAR(L1)&" TOT",IF(YEAR(L1+7)<>YEAR(L1),"",L1+7)),IF(LEFT(M1,4)="QTR4","",IF(MOD(MONTH(L1),3)=0,"QTR"&MONTH(L1)/3&" TOT",IF(YEAR(M1+7)<>YEAR(M1),"",M1+7)))))) | =IF(O1="","",IF(ISNUMBER(O1),IF(MONTH(O1+7)<>MONTH(O1),UPPER(TEXT(O1,"mmm"))&" TOT",O1+7),IF(LEFT(O1,3)="QTR",IF(LEFT(O1,4)="QTR4",YEAR(M1)&" TOT",IF(YEAR(M1+7)<>YEAR(M1),"",M1+7)),IF(LEFT(N1,4)="QTR4","",IF(MOD(MONTH(M1),3)=0,"QTR"&MONTH(M1)/3&" TOT",IF(YEAR(N1+7)<>YEAR(N1),"",N1+7)))))) | =IF(P1="","",IF(ISNUMBER(P1),IF(MONTH(P1+7)<>MONTH(P1),UPPER(TEXT(P1,"mmm"))&" TOT",P1+7),IF(LEFT(P1,3)="QTR",IF(LEFT(P1,4)="QTR4",YEAR(N1)&" TOT",IF(YEAR(N1+7)<>YEAR(N1),"",N1+7)),IF(LEFT(O1,4)="QTR4","",IF(MOD(MONTH(N1),3)=0,"QTR"&MONTH(N1)/3&" TOT",IF(YEAR(O1+7)<>YEAR(O1),"",O1+7)))))) | =IF(Q1="","",IF(ISNUMBER(Q1),IF(MONTH(Q1+7)<>MONTH(Q1),UPPER(TEXT(Q1,"mmm"))&" TOT",Q1+7),IF(LEFT(Q1,3)="QTR",IF(LEFT(Q1,4)="QTR4",YEAR(O1)&" TOT",IF(YEAR(O1+7)<>YEAR(O1),"",O1+7)),IF(LEFT(P1,4)="QTR4","",IF(MOD(MONTH(O1),3)=0,"QTR"&MONTH(O1)/3&" TOT",IF(YEAR(P1+7)<>YEAR(P1),"",P1+7)))))) | =IF(R1="","",IF(ISNUMBER(R1),IF(MONTH(R1+7)<>MONTH(R1),UPPER(TEXT(R1,"mmm"))&" TOT",R1+7),IF(LEFT(R1,3)="QTR",IF(LEFT(R1,4)="QTR4",YEAR(P1)&" TOT",IF(YEAR(P1+7)<>YEAR(P1),"",P1+7)),IF(LEFT(Q1,4)="QTR4","",IF(MOD(MONTH(P1),3)=0,"QTR"&MONTH(P1)/3&" TOT",IF(YEAR(Q1+7)<>YEAR(Q1),"",Q1+7)))))) |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Simply change the year in cell B1 and the headers will change accordingly. You can then copy the full range and paste it if you want to fix the values.
You could also use a macro, but i like stupidly complicated formula.
Being agreed with @RichardCook, it is something you need to use one time per year, but anyway it is worth to take sometime, for some people who don't like manual repetitive tasks, to define a formula you can use more than one time. Here is an approach that creates a LAMBDA function GEN_TITLE(Generate the title). For example, you can define it in Name Manager if you want to reuse it. It assumes there is no Excel version constraint as per the tag listed in the question. It is an array solution that spills the entire result for a given input year (yr)
=LET(GEN_TITLE, LAMBDA(yr, LET(ref,DATE(yr,1,8-WEEKDAY(DATE(yr,1,1),2)),
d,ref+SEQUENCE(,52,0,7), out, DROP(REDUCE(ref,d, LAMBDA(ac,a,
LET(cMonth,MONTH(a), last,MAX(ac),lMonth,MONTH(last), qtr,INT(lMonth/3),
HSTACK(ac,IF(cMonth = lMonth,a,IF(MOD(lMonth,3)=0,
HSTACK(TEXT(last,"mmm")&" TOT", "QTR"&qtr&" TOT", a),
HSTACK(TEXT(last,"mmm")&" TOT",a))))))),,1),
HSTACK(TEXT(out,"mmm-dd"), TEXT(TAKE(out,,-1),"mmm") &" TOT", "QTR4 TOT"))),
GEN_TITLE(2023))
Here is the output:
What GEN_TITLE does is for a given year (yr), finds the first Sunday of the year (ref, i.e. reference date). The following is the logic to find the first Sunday within a given month, for a date (d):
DATE(YEAR(d),MONTH(d),8-WEEKDAY(d,2))
Having the reference date, a year has 52 weeks, so we can generate all the Sundays of the year (d) as follows: ref+SEQUENCE(,52,0,7). Now we use REDUCE/HSTACK pattern(1) to iterate over each element of d.
On each iteration it calculates the current month (cMonth), the last valid date added (last) to the accumulator (ac). Since we add also the totals no all elements added are valid dates (numbers). All valid dates concatenated via HSTACK to ac are in ascending order, so it is enough to find the maximum number among all valid numbers (remember MAX function only takes into account numbers from the input), to get the last valid date.
Now we build the logic to identify the end of the month and the quarter using nested IF conditions. If the cMonth and lMonth (last month) are the same, we add the current Sunday(a) , otherwise if the last month is divisible by 3 (end of the quarter), then we add three elements: Last total month header, quarter header, plus a. If not, then we add just Last total month header plus a.
The name out, has the expected output, but we need to add the last header information for December (end of Month and Quarter). This is what we do with the last HSTACK call and convert also the dates in to the desired format (mmm-dd) via TEXT function. The conversion is optional, you can format the cells of the output using Excel date format mmm-dd, so you keep the data as date data type (much better, if you need to use the header for other calculations in the future).
(1) Check my answer to the following question: how to transform a table in Excel from vertical to horizontal but with different length to understand this pattern.
