Dynamic calendar and sum conditionally formatted cells

[harieta]

Hi,
I'm working on dynamic calendar that has weekends and Bank Holidays conditionally formatted (highlighted)
I'm struggling with formula that would sum values each week depending if it's normal weekday, weekend or Bank Holiday

I have 4 conditional formatting rules in this order
grey out days that don't belong to current month
highlight bank holidays in orange
highlight weekends in green
highlight weekdays in white

I have been using VBA for summing conditional formatted cells

Function SumConditionColorCells(CellsRange As Range, ColorRng As Range)
Dim Bambo As Boolean
Dim dbw As String
Dim CFCELL As Range
Dim CF1 As Single
Dim CF2 As Double
Dim CF3 As Long
Bambo = False
For CF1 = 1 To CellsRange.FormatConditions.Count
If CellsRange.FormatConditions(CF1).Interior.ColorIndex = ColorRng.Interior.ColorIndex Then
Bambo = True
Exit For
End If
Next CF1
CF2 = 0
CF3 = 0
If Bambo = True Then
For Each CFCELL In CellsRange
dbw = CFCELL.FormatConditions(CF1).Formula1
dbw = Application.ConvertFormula(dbw, xlA1, xlR1C1)
dbw = Application.ConvertFormula(dbw, xlR1C1, xlA1, , ActiveCell.Resize(CellsRange.Rows.Count, CellsRange.Columns.Count).Cells(CF3 + 1))
If Evaluate(dbw) = True Then CF2 = CF2 + CFCELL.Value
CF3 = CF3 + 1
Next CFCELL
Else
SumConditionColorCells = ""
Exit Function
End If
SumConditionColorCells = CF2
End Function


now my issue:
helper column should only sum weekdays (exclude Bank Holidays) but even if I reference in formula to cell with white colour, it still takes value of cell that is coloured in orange (Bank Holiday)
is there another way to sum values of cells but their formatted colour? Does my udf needs amending?
my mini sheets shows manually entered values in columns BN to BR to show what I need the formula to calculate, with the ability to adapt to a different month (and different allocation of Bank Holiday in a week - Monday, Friday, Thursday)


Can you please guise where to start editing

Thank you

Book1
	AU	AV	AW	AX	AY							BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX
1
2	20/03/2024	21/03/2024	22/03/2024	23/03/2024	24/03/2024							25/03/2024	26/03/2024	27/03/2024	28/03/2024	29/03/2024	30/03/2024	31/03/2024	Total O/T	helper	O/T @ flat rate	O/T @ 1.5	Weekend	Bank Holiday					bank holidays
3													5	3		6		7	21.00	8		0	7	6					01/01/2024
4																													29/03/2024
5																													01/04/2024
6																													06/05/2024
7																													27/05/2024
8																													26/08/2024
9
10
11
12
13
14
15
16
jan

Cell Formulas
Range	Formula
BF2:BL2	BF2	=SEQUENCE(,7,AY2+1,1)
BM3	BM3	=SUM(BF$3:BL$3)
BP3	BP3	=IF($C3=$D3,$N3,$N3-$O3)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:BL60	Expression	=MONTH(F$2)<>MONTH($U$2)	text	NO
BF2:BL60	Expression	=ISNUMBER(VLOOKUP(BF$2,$BW$3:$BW$8,1,0))	text	NO
AF2:AL60,AS2:AY60,BF2:BL60,S2:Y60,F2:L60	Expression	=WEEKDAY(F$2,2)>5	text	NO
AF2:AL60,AS2:AY60,BF2:BL60,S2:Y60,F2:L60	Expression	=OR(WEEKDAY(F$2)=2,WEEKDAY(F$2)=3,WEEKDAY(F$2)=4,WEEKDAY(F$2)=5,WEEKDAY(F$2)=6)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
BF2:BL60	Custom	=MONTH(AZ2)<>MONTH($AY$2)
BM2	Any value
BM3:BM60	Custom	=MONTH(BM2)<>MONTH($AY$2)

 

[KRice]

Thanks. Which formula is generating the #VALUE! error? Which version of Excel is being used?

 

[harieta]

Hi Kirk,

I tried in Office 2021 and 365
I have created the array for weekday, weekend and Bank holiday and I have added a formula to helper and o/t@1.75 o/t @2.25 cell - this is where i have value error

overtime.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX
3
4	Contract hours				Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu							1	1	1	1	1	0	0
5					01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29
6	28.00				5.00		5.00	5.00		5.00	5.00	5.00																												#REF!	this is week 1
7																																								#VALUE!	this is week 2
8
finished

Cell Formulas
Range	Formula
AQ4	AQ4	=NETWORKDAYS.INTL(+L$5#,+L$5#,"0000011",BH)
AR4:AW4	AR4	=NETWORKDAYS.INTL(+F$5#,+F$5#,"0000011",BH)
E5	E5	=DATEVALUE(1&A2&B2)-WEEKDAY(DATEVALUE(1&A2&B2),3)
F5:AO5	F5	=E5+1
AQ6	AQ6	=SUMPRODUCT(E6:K6,NETWORKDAYS.INTL(+L$4#,+L$4#,"0000011",BH))
AQ7	AQ7	=SUMPRODUCT(L6:R6,NETWORKDAYS.INTL(+L$5#,+L$5#,"0000011",BH))

Named Ranges
Name	Refers To	Cells
BH	=data!$A$16:$A$23	AQ6:AQ7, AQ4:AW4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E4:AO50	Expression	=MONTH(E$5)<>MONTH($U$5)	text	YES
E4:AO50	Expression	=WEEKDAY(E$5,2)>5	text	NO
E4:AI50,AJ4:AO4,AM6:AO50,G5:AO5	Expression	=ISNUMBER(VLOOKUP(E$4,data!$A$15:$A$23,1,0))	text	NO

 

[KRice]

Okay, this shows why being able to see a snapshot of your worksheet is so important...I wouldn't have known otherwise. Let's look at your formulas that are returning an error:

SUMPRODUCT(L6:R6,NETWORKDAYS.INTL(+L$5#,+L$5#,"0000011",BH))

I see that you must have a named range called BH, so I created one named the same in my worksheet and populated it with a few dates. Beyond that, there is another significant issue:
The NETWORKDAYS.INTL function has a start and end date, and right now, this is set to +L$5# for both start and end dates. The # symbol means to take the entire array that spills from the source formula in cell L$5, but if we look at cell L$5, we see that 1) it isn't the starting point---the source---for a spilling array, and 2) it isn't even part of a spilling array. So this syntax is incorrect in this case. What you have in H5 is a date starting point, and then you construct subsequent dates by dragging a formula across the row. A spilling array, on the other hand, would have a single source formula in H5 that would form the entire array of dates, and all of the dates would spill across the row without having to drag a formula into each cell.

You've adjusted the range reference for overtime hours correctly ((E6:K6 for week 1 and L6:R6 for week 2). Note that each of these is 1 row by 7 columns. The SUMPRODUCT function that they are passed into will want another range reference/array whose dimensions are the same (1 row by 7 columns). So we can fix these formulas several ways. If you want to do this manually. , see examples in AQ6 and AQ7. Alternatively, you could specify the entire range for dates (35 columns wide) and the entire range for hours (also 35 columns wide), and then use a formula to extract only the groups of 7 that matter for each week. One formula that will do this is:

SEQUENCE(,7,(AT6-1)*7+1)

...which creates a 7 column wide array, beginning at (AT6-1)*7+1, where AT6 is the cell address with the week number of interest. For week 1 (AT6=1), we get (1-1)*7+1, which is 0*7+1 or just 1. So the array would be {1,2,3,4,5,6,7}. For week 2, we get (2-1)*7+1, which is 1*7+1, or 8, so the array would be {8.9.10,11,12,13,14}.

This array of column numbers needed for each week can be passed to the INDEX function (or the CHOOSECOLS function) to extract the relevant subsets of dates and hours for each week. The formula in AS6 shows this, but you'll see that there is quite a bit of redundancy, as the same column array is needed three times, and this makes the formula messier, more difficult to understand, and more prone to editing errors. An alternative is the wrap these formula components inside a LET function and define a working variable called "cols" (the array of relevant column indexes).. It is defined just once, and then "cols" can be used multiple times where needed. Similarly, the array of dates for each week is used twice, so we can define it one and assign it to the "dts" working variable, and then use it where needed. So the formula in AS7 incorporates these ideas, but it otherwise the functional equivalent of the AS6 formula.

This very issue is why I asked so many questions about your process. You mentioned several times that processing was done monthly, so I thought you could easily apply a spilling array for the entire month and compute the relevant sums for O/T on weekdays, weekends, and holidays. But its more complicated than that: you process monthly in arrears, but you need to evaluate by week, and then consider within the weeks only those days that actually belong to the month of interest. I'm attempting to revise the formulas to do this. The simplest would be to create a 42 column wide (more on this in the next paragraph) helper table that could be hidden. And that

I wanted to comment to something you mentioned earlier...something about needing 5 weeks for each month. Wouldn't you need 6 weeks in some cases? For example a 30 or 31 day month that begins on Sunday, the last day of "week 1" would be followed by 28 more days in weeks 2 through 5, for a total of 29 days up to that point...and then 1 or 2 more days would spill into the 6th week.

 

[KRice]

I guess I accidentally posted before I was done the next-to-last paragraph in my last post. Picking up there...
The simplest would be to create a 42 column wide (more on this in the next paragraph) helper table that could be hidden. And that helper table would be used by formulas similar to what we've discussed here to process the weekday overtime hours by week. I'd like to do this without a helper table, but that requires some additional steps to handle correctly inside a formula...so I'm still looking at that option.

And here are the formulas I referred to, as well as the date spilled from each of the INDEX/SEQUENCE formulas:

MrExcel_20240109b.xlsx
	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA
5				Week	Dates
6	13	this is week 1	13	1	45348	45349	45350	45351	45352	45353	45354
7	15	this is week 2	15	2	45355	45356	45357	45358	45359	45360	45361
8				3	45362	45363	45364	45365	45366	45367	45368
9				4	45369	45370	45371	45372	45373	45374	45375
10				5	45376	45377	45378	45379	45380	45381	45382
Sheet4

Cell Formulas
Range	Formula
AQ6	AQ6	=SUMPRODUCT(E6:K6,NETWORKDAYS.INTL(+E5:K5,+E5:K5,"0000011",BH))
AQ7	AQ7	=SUMPRODUCT(L6:R6,NETWORKDAYS.INTL(+L5:R5,+L5:R5,"0000011",BH))
AS6	AS6	=SUMPRODUCT(INDEX($E$6:$AM$6,,SEQUENCE(,7,(AT6-1)*7+1)),NETWORKDAYS.INTL(INDEX($E$5:$AM$5,,SEQUENCE(,7,(AT6-1)*7+1)),INDEX($E$5:$AM$5,,SEQUENCE(,7,(AT6-1)*7+1)),"0000011",BH))
AS7	AS7	=LET(cols,SEQUENCE(,7,(AT7-1)*7+1), dts, INDEX($E$5:$AM$5,,cols), SUMPRODUCT(INDEX($E$6:$AM$6,,cols),NETWORKDAYS.INTL(dts,dts,"0000011",BH)))
AU6:BA10	AU6	=INDEX($E$5:$AM$5,,SEQUENCE(,7,(AT6-1)*7+1))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
BH	=Sheet4!$BD$2:$BD$7	AS6:AS7, AQ6:AQ7

 

[harieta]

oh gosh, I think it's a lot for my little head to comprehend There was me thinking it's an easy task

I wanted to comment to something you mentioned earlier...something about needing 5 weeks for each month. Wouldn't you need 6 weeks in some cases? For example a 30 or 31 day month that begins on Sunday, the last day of "week 1" would be followed by 28 more days in weeks 2 through 5, for a total of 29 days up to that point...and then 1 or 2 more days would spill into the 6th week.

I found out that i need more than 5 weeks after my draft when I was looking at all months and run out of columns thank you

I see that you must have a named range called BH, so I created one named the same in my worksheet and populated it with a few dates.

yes I have another tab with months listed (for drop down list) and table named BH that has listed all Bank Holidays

So we can fix these formulas several ways. If you want to do this manually. , see examples in AQ6 and AQ7.

I think I can do that one, at least I now know where this formula comes from


With the second version, am I correct to assume the array AU6:BU10 changes automatically each month as it refers to cells E5 : EO5

But its more complicated than that: you process monthly in arrears, but you need to evaluate by week, and then consider within the weeks only those days that actually belong to the month of interest.

Unfortunately, yes. I know it makes it messy and overcalculated but this ensures accuracy when paying and I wouldn't want to give those spreadsheet out knowing this is "cutting corners" by simplifying and just looking on the data as whole month.

With regards to formula in AS7 it looks so advanced that I wouldn't even know where to start to understand elements of it.

You've been kind enough to work with me on my problem, thanks a million!. Whichever way you think it would work I am extremely grateful

 

[KRice]

I'm sorry to have kept you waiting...I set this aside to think about the weekday adjustments by week for non-FTE employees. I've also revised the approach taken for most of the quantities, and I am presenting an option to consider that keeps most of the data on the main sheet, rather than separate monthly tabs.

The main worksheet in envisioned as a multi-month, or perhaps year-long or multi-year, table whose column headings (dates) are an array formed from a single formula. Currently, that formula assumes the data log will begin on the Monday belonging to data start date specified in cell H1. The reason for this is to preserve overtime hour entries for the entire week, even if the first day(s) of that week belong to a prior year or month. Additionally, the user specifies (in cell H2) the number of months that should be covered by the date array. The date array-generating formula determines the Sunday after the last day of the nth month, again to preserve all overtime hour entries for that last week, even if the last day(s) of that week belong to the next year or month. This is done by the formula in cell M5, which spills the dates array to the right:

The months that are found in the data log are listed on a separate Lists worksheet, using a formula that spills all of them dynamically in mmm-yyyy text format, and this can be used by a drop down selector on the main worksheet using Data Validation, with the Lists worksheet cell address referenced as the source.

The Lists worksheet is also used to maintain a list of bank holidays, beginning in cell A2. Because you will need to add dates to this list, its length might change, so rather than trying to maintain a range reference for a changing list length, I would do one of two things: either enter bank holiday dates in a dedicated table and then use structured references to specify the bank holiday date column name, or declare an overly large fixed range and form a dynamic list of bank holiday dates. I've taken the latter approach, specifying a large fixed range reference of Lists!$A$2:$A$100 and assigning this range to a variable called "HolidayRng". Later in the formula, this range, which contains dates and blank cells, is filtered down to display only the array of dates.

The relationship between the contractual weekly workload (in hours) and that of a full time equivalent position (also in hours) is shown in columns C and D for each row of data. These cells are assigned to variables ContractWorkload and FTEWorkload in the main formula. For convenience, another formula component inside the main LET formula assigns a variable called "ShortFTE_WkD_hrs"...a somewhat long variable name representing the difference---the shortage---between an FTE and the employee's contractual workload.

The overtime hours data on each row begins in the same column as the first date, so a dynamic array of O/T hours is created with the formula. For O/T data on row 6, the formula appears like this:
Data, INDEX(6:6,,COLUMN(Dates)) ...where the entire range of O/T data on that row is assigned to a variable called Data. The advantage in using a dynamic array is to accommodate a changing data table size without needing to revise the data range reference.

The user sets a query month in cell B4. The month-year choice is offered through a drop down list described earlier. Recall that this choice is text having the format mmm-yyyy, so the text is immediately subjected to a mathematical operation where we add 0 to it, which at first does not seem to make sense, but this causes Excel to consider mmm-yyyy as a number---a date---and automatically makes the interpretation that mmm-yyyy is the first day of whatever month and year are chosen. This date value (a number) is assigned to the variable qryMo (short for query month).

MrExcel_20240221.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M																											AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV
1								1/1/2024	Data Begin Mo-Yr		Data Log
2								4	Num Mos
3											ISOWeek	1	1																											5	5	5	5	5	5	5	6	6	6	6	6	6	6	7	7	7	7	7	7	7	8	8	8	8	8	8	8	9	9	9	9	9	9	9
4	Query Month®	Feb-2024			Month Sums						Day# of Wk	1	2																											1	2	3	4	5	6	7	1	2	3	4	5	6	7	1	2	3	4	5	6	7	1	2	3	4	5	6	7	1	2	3	4	5	6	7
5	Name	Employee ID	Contract (h/wk)	FT Equiv (h/wk)	Total O/T	Weekdays O/T @ Flat	Weekdays O/T @ 1.5	Weekend	Bank Holiday	Double Check Total	Date	1/1/2024	1/2/2024																											1/29/2024	1/30/2024	1/31/2024	2/1/2024	2/2/2024	2/3/2024	2/4/2024	2/5/2024	2/6/2024	2/7/2024	2/8/2024	2/9/2024	2/10/2024	2/11/2024	2/12/2024	2/13/2024	2/14/2024	2/15/2024	2/16/2024	2/17/2024	2/18/2024	2/19/2024	2/20/2024	2/21/2024	2/22/2024	2/23/2024	2/24/2024	2/25/2024	2/26/2024	2/27/2024	2/28/2024	2/29/2024	3/1/2024	3/2/2024	3/3/2024
6	Marie	100	28	37	30	17	8	2	3	30		1	1																																													4			3		2	1	1	1	1	11	1	2	3	4		2
7	Employee2	101	37	37	130	0	91	27	12	130		2	4																											7	5	2	7	5	2	5	5	5	2	4	7	5	2	7	5	2	5	5	2	4	7	5	2	7	5	2	5	2	4	7	5	2	7	5
8	Employee3	102	22	37	56	35	1	11	9	56																														5	3	2	6	7	3	1			3	7	3	2	1							1	2	1	2			3		5	4	2	3	1	2	4
OTlog

Cell Formulas
Range	Formula
L3:EG3	L3	=ISOWEEKNUM(L5#)
L4:EG4	L4	=WEEKDAY(L5#,2)
L5:EG5	L5	=LET(MonOfFirst,WORKDAY.INTL($H$1+1,-1,"0111111"), NumDays, WORKDAY.INTL(EDATE($H$1,$H$2)-2,1,"1111110")-MonOfFirst+1, MonOfFirst+SEQUENCE(,NumDays,0))
E6:J8	E6	=LET(Dates, $L$5#, Data, INDEX(6:6,,COLUMN(Dates)),ContractWorkload, $C6, FTEWorkload, $D6, HolidayRng, Lists!$A$2:$A$100, qryMo, $B$4 + 0, RateFactor, {1,1.5,1.75,2.25}, commentRateFactor, {"flat rate","weekday","weekend","bank holiday"}, Holidays, FILTER(HolidayRng, HolidayRng <> ""), ShortFTE_WkD_hrs, FTEWorkload - ContractWorkload, MoFullWks, FILTER(VSTACK(Dates, Data), (Dates >= WORKDAY.INTL(qryMo + 1, -1, "0111111")) * (Dates <= WORKDAY.INTL(EOMONTH(qryMo, 0) - 1, 1, "1111110"))), FW_dts, CHOOSEROWS(MoFullWks, 1), FW_hrs, CHOOSEROWS(MoFullWks, 2), qMoFW_dts, EOMONTH(FW_dts,0)=EOMONTH(qryMo,0), MoWkE_OT, SUM(FILTER(FW_hrs, qMoFW_dts * (WEEKDAY(FW_dts,2)>5))), MoBH_OT, SUM(FILTER(FW_hrs, qMoFW_dts * ISNUMBER(MATCH(FW_dts,Holidays,0)),0)), FW_WkD_hrs, IF(NETWORKDAYS.INTL(FW_dts, FW_dts, "0000011", Holidays), FW_hrs, 0), commentFW_WkD_hrs,{"overwrites with 0 hrs for all WkE & BH"}, revFW_WkD_hrs, LET(a, WRAPROWS(FW_WkD_hrs, 7), b, ShortFTE_WkD_hrs, c, SCANBYROW(0, a, LAMBDA(x,y, x + y)), d, c - b, e, IF(c > d, d, c), f, IF(e > a, a, e), g, IF(f > 0, f, 0), TOROW(g)), MoWkD_OT, SUM(FILTER(revFW_WkD_hrs, qMoFW_dts)), MoWkD_Flat, SUM(FILTER(FW_WkD_hrs, qMoFW_dts)) - MoWkD_OT, MoAll_OT,SUM(MoWkD_Flat, MoWkD_OT, MoWkE_OT, MoBH_OT), MoAll_OTcheck,SUM(FILTER(FW_hrs,qMoFW_dts)), FinalOutput, HSTACK(MoAll_OT,MoWkD_Flat, MoWkD_OT, MoWkE_OT, MoBH_OT, MoAll_OTcheck), FinalOutput)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L3:ZZ12	Expression	=AND(ISNUMBER(L$5),WEEKDAY(L$5,2)>5)	text	NO
L3:ZZ12	Expression	=ISNUMBER(MATCH(L$5,Lists!$A$2:$A$100,0))	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B4	List	=Lists!$B$2#

MrExcel_20240221.xlsx
	A	B
1	Bank Holidays	MoYr List
2	2/19/2024	Jan-2024
3	3/29/2024	Feb-2024
4	4/1/2024	Mar-2024
5	5/6/2024	Apr-2024
6	5/27/2024	May-2024
7	1/1/2024
8	1/15/2024
9	2/2/2024
Lists

Cell Formulas
Range	Formula
B2:B6	B2	=UNIQUE(TRANSPOSE(TEXT(OTlog!L5#,"mmm-yyyy")))
Dynamic array formulas.

The trickiest part of the main formula involves splitting the array of full weeks that cover the query month into individual weeks that can be analyzed to determine when the FTE hours short have been accounted for. That component of the main formula is set off within a separate LET function, and in there is an unusual function called SCANBYROW. The methodology used for the week-by-week evaluation requires an array of the cumulative sum of O/T hours for each full week. Ideally this could be accomplished with a BYROW LAMBDA helper function, where SCAN processes each array element and adds it to the accumulating sum, but BYROW will return only a single value, not an array...and we need the array of accumulating sequential sums. A convenient way to obtain this relies on a custom LAMBDA function called SCANBYROW, created by @tboulden and posted to this forum:

SCANBYROW

Performs a SCAN(initial_value,row,fn) on each row of array. Corresponding SCANBYCOL is also shown. BYROW/BYCOL are limited in returning a single value per row/column respectively, whereas SCAN returns an array of the same dimensions as the array being SCANned. We can overcome this limitation by...

www.mrexcel.com

To use the function as I have, the function needs to be created by adding the name SCANBYROW via the Name Manager (or Advanced Formula Environment) and then pasting the following into the "Refers to" block (Name Manger) or "Function definition" (AFE):

=LET(
    rows_,ROWS(array),
    cols_,COLUMNS(array),
    row_thunks,
        BYROW(array,
            LAMBDA(row_,
                LAMBDA(SCAN(initial_value,row_,fn))
            )
        ),
    MAKEARRAY(rows_,cols_,
        LAMBDA(i,j,
            INDEX(
                INDEX(row_thunks,i,1)(),
                1,
                j
            )
        )
    )
)

Once that is done, SCANBYROW is available like any other function. It has three arguments: SCANBYROW(initial_value, array, fn). We seed the initial value with 0 (1st argument); pass in a row of weekday O/T hours (where the row represents a full week) (2nd argument), and then define the LAMBDA function to generate an accumulating sum...LAMBDA(x,y, x + y)...(3rd argument). And the SCANBYROW function builds the desired output array from individual results returned by the BYROW function. This process is repeated for each of the full weeks that cover the entire query month.