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]

Have you looked into the NETWORKDAYS.INTL function? I believe that would be a better approach. See example here:
How to exclude weekends & bank holidays

 

[harieta]

Have you looked into the NETWORKDAYS.INTL function? I believe that would be a better approach. See example here:
How to exclude weekends & bank holidays

Please forget I'm I'm missing something but networkdays.intl will calculate difference between start date and end date?
I have number of working hours entered for certain days of the week that needs adding by the "day type" - would this work?

 

[KRice]

Sorry...I should have explained better. The comment about using NETWORKDAYS.INTL is that it already has functionality for identifying working days...and yes, you are correct, the number of working days between a start and end date. But if we specify the same date as both a start and end date, and that particular day happens to be a regular working day, the result will be 1. And if that particular day is a weekend or holiday as defined within the function's arguments, then the result will be a 0. So we can use NETWORKDAYS.INTL to create arrays of 1's and 0's that show weekend days, holidays, days that perhaps are both a weekend day and a holiday, as well as regular working days. We just need to be judicious about defining what the "weekend" looks like for some of these, and completely ignore holidays for others. I'm showing two implementations of this idea: 1) One is a formula that is dragged across the row, and it returns a result for each date above it in row 2...this isn't ideal, but illustrates the basic idea. 2) The other is a spilling formula capable of creating the entire array for the dates specified (note that in order to make NETWORKDAYS.INTL accept a range as an argument, we can use the unary plus operator to pass the range as an array to the function). This version is more useful and can be used inside a SUMPRODUCT function to obtain the sums desired (see blue-shaded cells).

MrExcel_20240109b.xlsx
	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW
1
2		3/25/2024	3/26/2024	3/27/2024	3/28/2024	3/29/2024	3/30/2024	3/31/2024	Total O/T	helper	O/T @ flat rate	O/T @ 1.5	Weekend	Bank Holiday					bank holidays
3			5	3		6		7	21	8		0	7	6					1/1/2024
4																			3/29/2024
5	weekend	0	0	0	0	0	1	1		7									4/1/2024
6	holiday	0	0	0	0	1	0	0		6									5/6/2024
7	weekend & holiday	0	0	0	0	0	0	0		0									5/27/2024
8	working weekdays	1	1	1	1	0	0	0		8									8/26/2024
9
10	spilling versions
11	weekend	0	0	0	0	0	1	1		7
12	holiday	0	0	0	0	1	0	0		6
13	weekend & holiday	0	0	0	0	0	0	0		0
14	working weekdays	1	1	1	1	0	0	0		8
15
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)
BF5:BL5	BF5	=NETWORKDAYS.INTL(BF$2,BF$2,"1111100")
BF6:BL6	BF6	=--NOT(NETWORKDAYS.INTL(BF$2,BF$2,"0000000",$BW$3:$BW$8))
BF7:BL7	BF7	=(NETWORKDAYS.INTL(BF$2,BF$2,"1111100")*NOT(NETWORKDAYS.INTL(BF$2,BF$2,"0000000",$BW$3:$BW$8)))
BF8:BL8	BF8	=NETWORKDAYS.INTL(BF$2,BF$2,"0000011",$BW$3:$BW$8)
BN5:BN8	BN5	=SUMPRODUCT(BF$3:BL$3,BF5:BL5)
BF11:BL11	BF11	=NETWORKDAYS.INTL(+$BF$2#,+$BF$2#,"1111100")
BF12:BL12	BF12	=--NOT(NETWORKDAYS.INTL(+$BF$2#,+$BF$2#,"0000000",$BW$3:$BW$8))
BF13:BL13	BF13	=(NETWORKDAYS.INTL(+$BF$2#,+$BF$2#,"1111100")*NOT(NETWORKDAYS.INTL(+$BF$2#,+$BF$2#,"0000000",$BW$3:$BW$8)))
BF14:BL14	BF14	=NETWORKDAYS.INTL(+$BF$2#,+$BF$2#,"0000011",$BW$3:$BW$8)
BN11	BN11	=SUMPRODUCT($BF$3:$BL$3,NETWORKDAYS.INTL(+$BF$2#,+$BF$2#,"1111100"))
BN12	BN12	=SUMPRODUCT($BF$3:$BL$3,--NOT(NETWORKDAYS.INTL(+$BF$2#,+$BF$2#,"0000000",$BW$3:$BW$8)))
BN13	BN13	=SUMPRODUCT($BF$3:$BL$3,(NETWORKDAYS.INTL(+$BF$2#,+$BF$2#,"1111100")*NOT(NETWORKDAYS.INTL(+$BF$2#,+$BF$2#,"0000000",$BW$3:$BW$8))))
BN14	BN14	=SUMPRODUCT($BF$3:$BL$3,NETWORKDAYS.INTL(+$BF$2#,+$BF$2#,"0000011",$BW$3:$BW$8))
Dynamic array formulas.

 

[harieta]

Sorry...I should have explained better. The comment about using NETWORKDAYS.INTL is that it already has functionality for identifying working days...and yes, you are correct, the number of working days between a start and end date. But if we specify the same date as both a start and end date, and that particular day happens to be a regular working day, the result will be 1. And if that particular day is a weekend or holiday as defined within the function's arguments, then the result will be a 0. So we can use NETWORKDAYS.INTL to create arrays of 1's and 0's that show weekend days, holidays, days that perhaps are both a weekend day and a holiday, as well as regular working days. We just need to be judicious about defining what the "weekend" looks like for some of these, and completely ignore holidays for others. I'm showing two implementations of this idea: 1) One is a formula that is dragged across the row, and it returns a result for each date above it in row 2...this isn't ideal, but illustrates the basic idea. 2) The other is a spilling formula capable of creating the entire array for the dates specified (note that in order to make NETWORKDAYS.INTL accept a range as an argument, we can use the unary plus operator to pass the range as an array to the function). This version is more useful and can be used inside a SUMPRODUCT function to obtain the sums desired (see blue-shaded cells).

MrExcel_20240109b.xlsx
	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW
1
2		3/25/2024	3/26/2024	3/27/2024	3/28/2024	3/29/2024	3/30/2024	3/31/2024	Total O/T	helper	O/T @ flat rate	O/T @ 1.5	Weekend	Bank Holiday					bank holidays
3			5	3		6		7	21	8		0	7	6					1/1/2024
4																			3/29/2024
5	weekend	0	0	0	0	0	1	1		7									4/1/2024
6	holiday	0	0	0	0	1	0	0		6									5/6/2024
7	weekend & holiday	0	0	0	0	0	0	0		0									5/27/2024
8	working weekdays	1	1	1	1	0	0	0		8									8/26/2024
9
10	spilling versions
11	weekend	0	0	0	0	0	1	1		7
12	holiday	0	0	0	0	1	0	0		6
13	weekend & holiday	0	0	0	0	0	0	0		0
14	working weekdays	1	1	1	1	0	0	0		8
15
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)
BF5:BL5	BF5	=NETWORKDAYS.INTL(BF$2,BF$2,"1111100")
BF6:BL6	BF6	=--NOT(NETWORKDAYS.INTL(BF$2,BF$2,"0000000",$BW$3:$BW$8))
BF7:BL7	BF7	=(NETWORKDAYS.INTL(BF$2,BF$2,"1111100")*NOT(NETWORKDAYS.INTL(BF$2,BF$2,"0000000",$BW$3:$BW$8)))
BF8:BL8	BF8	=NETWORKDAYS.INTL(BF$2,BF$2,"0000011",$BW$3:$BW$8)
BN5:BN8	BN5	=SUMPRODUCT(BF$3:BL$3,BF5:BL5)
BF11:BL11	BF11	=NETWORKDAYS.INTL(+$BF$2#,+$BF$2#,"1111100")
BF12:BL12	BF12	=--NOT(NETWORKDAYS.INTL(+$BF$2#,+$BF$2#,"0000000",$BW$3:$BW$8))
BF13:BL13	BF13	=(NETWORKDAYS.INTL(+$BF$2#,+$BF$2#,"1111100")*NOT(NETWORKDAYS.INTL(+$BF$2#,+$BF$2#,"0000000",$BW$3:$BW$8)))
BF14:BL14	BF14	=NETWORKDAYS.INTL(+$BF$2#,+$BF$2#,"0000011",$BW$3:$BW$8)
BN11	BN11	=SUMPRODUCT($BF$3:$BL$3,NETWORKDAYS.INTL(+$BF$2#,+$BF$2#,"1111100"))
BN12	BN12	=SUMPRODUCT($BF$3:$BL$3,--NOT(NETWORKDAYS.INTL(+$BF$2#,+$BF$2#,"0000000",$BW$3:$BW$8)))
BN13	BN13	=SUMPRODUCT($BF$3:$BL$3,(NETWORKDAYS.INTL(+$BF$2#,+$BF$2#,"1111100")*NOT(NETWORKDAYS.INTL(+$BF$2#,+$BF$2#,"0000000",$BW$3:$BW$8))))
BN14	BN14	=SUMPRODUCT($BF$3:$BL$3,NETWORKDAYS.INTL(+$BF$2#,+$BF$2#,"0000011",$BW$3:$BW$8))
Dynamic array formulas.

Hi Kirk,

Thank you, the option 2 is doing the trick,
I have one question. The array was built around one week only - cells BF:BL
Do i need to create the array for each week separately.
In between the weeks i have these calculations so the calendar dates are spilled with those additional 6 "adding columns"

 

[KRice]

From your example, I wasn't sure how a solution would be implemented. I believe you must have employees listed down, row by row, and you're building a calendar out to the right, one week at a time. Each week is followed by six columns:

1. Total O/T,
2. A "helper", which I think is the sum of overtime hours incurred during normal weekdays,
3. O/T @ flat rate,
4. O/T @ 1.5,
5. Weekend, and
6. Bank Holiday.

I see formulas for items 1, 2, 5, and 6, but presumably items 3 and 4 multiply one or some of the O/T sums by a normal hourly rate. Where is that hourly rate found, and does it ever change over the course of the calendar dates being logged?

I see two basic approaches:

1. The first would be my preferred way, because I like to keep source data consolidated, and not interspersed with intermediate calculations. In this approach, dates for your entire fiscal year would spill across row 2, beginning in perhaps the 14th column of the worksheet. And every week, overtime hours are logged moving day by day to the right in the table. The initial columns in the worksheet would be used to document static information (employee name, employee ID, normal hourly pay rate, maybe another column or two of interest (department, project area, etc.)) and a summary of the dynamic information (results of the six calculation columns and perhaps a weekly total sum). Then you could have a week selector cell where the week of interest is specified, and the formulas would automatically select the days during the specified week and return the results to the calculation block fixed in the leftmost columns of the worksheet.
2. The second would be what you've presented: where you could copy and paste a single multi-column block, one week at a time. And that block would consist of seven columns representing the dates with entry cells below for logging hours, followed by the six computation columns and their formulas.

The easiest is #2, as it most closely resembles what you have presented. Another consideration: since you're using Excel 365, you could have the six computations spill the results using one formula. This wouldn't really reduce the number of calculations or file size, but it would consolidate all six of the calculations into one multi-part formula, which might be more convenient for maintenance and copying.

If you go with option #2, a notional example is shown here with relative range references to adapt to the correct week.

MrExcel_20240109b.xlsx
	AK	AL							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	BW
1
2		3/17/2024							3/18/2024	3/19/2024	3/20/2024	3/21/2024	3/22/2024	3/23/2024	3/24/2024	Total O/T	Working Weekdays	O/T @ flat rate	O/T @ 1.5	Weekend	Bank Holiday	3/25/2024	3/26/2024	3/27/2024	3/28/2024	3/29/2024	3/30/2024	3/31/2024	Total O/T	Working Weekdays	O/T @ flat rate	O/T @ 1.5	Weekend	Bank Holiday					bank holidays
3	Employee1								1	3	5	8	4	1	4	26	21		0	5	0		5	3		6		7	21	8		0	7	6					1/1/2024
4	Employee2								2	4	6	7	5	2	4	30	24		0	6	0	2	1	4	3	9	2.5	1.5	23	10		0	4	9					3/29/2024
5																																							4/1/2024
6																																							5/6/2024
7																																							5/27/2024
8																																							8/26/2024
janB

Cell Formulas
Range	Formula
AS2:AY2,BF2:BL2	AS2	=SEQUENCE(,7,AL2+1,1)
AZ3:AZ4,BM3:BM4	AZ3	=SUM(AS3:AY3)
BA3:BA4,BN3:BN4	BA3	=SUMPRODUCT(AS3:AY3,NETWORKDAYS.INTL(+AS$2#,+AS$2#,"0000011",$BW$3:$BW$8))
BC3:BC4,BP3:BP4	BC3	=IF($C3=$D3,$N3,$N3-$O3)
BD3:BD4,BQ3:BQ4	BD3	=SUMPRODUCT(AS3:AY3,NETWORKDAYS.INTL(+AS$2#,+AS$2#,"1111100"))
BE3:BE4,BR3:BR4	BE3	=SUMPRODUCT(AS3:AY3,--NOT(NETWORKDAYS.INTL(+AS$2#,+AS$2#,"0000000",$BW$3:$BW$8)))
Dynamic array formulas.

 

[KRice]

I'm curious...it appears that your weeks for time logging begin on Monday and end with a Saturday/Sunday weekend. But when does week 1 begin? There are different conventions...For example, are you using the ISO 8601 convention, where week 1 is whichever week has the first Thursday of January?...or some other convention?

 

[harieta]

Hi Kirk,
yes, you are right, employees in rows down,
hourly rate is not part of calculations as it differs for each employee,
helper was assessing number of hours worked on weekday (but not bank holiday) - from that the O/T @1.5 and O/T at flat rate is based on employee contract, if part time - then o/t is at flat rate until it reaches equivalent of full time , when over this is logged as O/T @ 1.5 That is why the weekly calculations are needed rather then summing the all hours for full month
each month is on separate tab - makes it more transparent for people who will be entering data to it.
if the formula does the calculation, there is no need for viewing weekly breakdown of the hours, just the total for calendar month
I also have to look at month end to check how many hours employee worked in current month if it crosses over to next one, so next month will take it into account when deciding on rate (flat or 1.5)
I'm not sure what is the best layout, if I go for continuous month dates does it mean that I can have 1 array and 5 individual calculations for each week at the end?

my week always starts on 1st of Jan, which happens to be Monday this year - if that is what you meant

 

[KRice]

I'll give some thought to your comments and respond back.

Regarding my question about "week 1"...I was trying to understand how you define the first week of the year, but maybe this isn't important based on your latest explanation. I wanted to ensure that if you sought details for a particular week number, that the week numbering scheme used by a formula would reflect your actual convention. I've had to look forward a couple of years to find some good examples to illustrate the point (you are using a Monday week start convention, and ISO 8601 also defines week starts as Mondays):

1. January 1, 2025 falls on a Wednesday, so that week, Mon-Sun, is 30 Dec 2024 - 5 Jan 2025. Is this week 1 of 2025? The ISO convention would call this week 1 of 2025 because the 1st Thursday of 2025 occurs during this week.
2. January 1, 2026 falls on a Thursday, so that week, Mon-Sun, is 29 Dec 2025 - 4 Jan 2026...is this week 1 of 2026? The ISO convention would call is week 1 of 2026 because the 1st Thursday of 2026 occurs during this week.
3. And finally, January 1, 2027 falls on a Friday, so that week, Mon-Sun, is 28 Dec 2026 - 3 Jan 2027...is this week 1 of 2027? The ISO convention considers this week 53 of 2026 because the 1st Thursday of 2027 occurs on January 7, 2027, the following week...so the following week would be week 1 of 2027.

With hours being logged on different worksheets by month, what happens when one month ends and another begins during the same week? Are the hours for that week logged on two different worksheets?

If you were to keep the time logging as a single contiguous block of data, you could extract subsets of that large data set for the various O/T sums of interest. For example, you might want to see results for a particular week number (which is where understanding the week numbering convention would be important), or perhaps you'd like to specify a start and end date and see results for data within that date range, or perhaps specify a month and return results for any dates falling within that month. This is the advantage in keeping source data consolidated, as it then becomes relatively easy to apply some filters to extract relevant portions of the data.

 

[KRice]

I'm not sure about my suggestion for a contiguous block of data, and the reason involves how the O/T @ flat rate and O/T @ 1.5 values are determined. Can you offer some more insight about those? I see you have:

=IF($C3=$D3,$N3,$N3-$O3)

What does this do? You also mentioned an employee contract declares someone as part time or full time, I think. So how do you determine when the O/T at flat rate reaches the equivalent of full time...what does this mean, and how is the apportionment done right now? Does this involve some combination of regular work hours and overtime hours. I'm trying to determine whether this assignment for either "flat rate" or "1.5" can be done automatically and if it would be compatible with the single data block idea.