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)

 

[harieta]

Hi Kirk,

Thank you for your message.
For the purpose of this exercise I don't think week numbers are not important as such as I'm looking at calendar month
As I grey out the dates that fall on next/previous month I also lock them for editing - each month have only record of o/t that falls on the specific month.
When one month crosses over to another I'm looking at last week's "helper" column - that tells me number of O/t during weekday in January and then add it to helper column of next month (February) but extracting that portion from the Flat rate and/or o/t @ 1.5 as it has already been paid for.

I have my formula for this in place in additional column named "paid last month"
in february this would be:
=IF(MONTH("last cell of week 5 in jan )<>MONTH(first cell of week 5 in jan),january week 5 helper cell,0)

The formula =IF($C3=$D3,$N3,$N3-$O3) is copied from another week of calendar but not correctly applied, as it's just draft version for time being. This is to check if employee is entitled to o/t @1.5 rate and its checking if contract hours = full time equivalent then the value for O/t gets from "helper" column, if not it deducts from helper the value of O/t @ flat rate
For flat rate i have a formula as well maybe not the best one but it does the trick
=IF($C3<$D3,MIN(helper cell,$D3-$C3),"") where c and d cells are the contract hours and full time equivalent

 

[KRice]

Unfortunately, I don't understand your explanation, as it refers to cells and columns that aren't shown or described. Let me ask a different question. If the daily overtime log for each employee were in one long continuous block of cells similar to this, and extending out to the right for future dates:

MrExcel_20240109b.xlsx
	L	M	N	O	P	Q	R	S	T	U
1		Data Log
2
3
4
5		Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue
6		1/1/2024	1/2/2024	1/3/2024	1/4/2024	1/5/2024	1/6/2024	1/7/2024	1/8/2024	1/9/2024
7	Employee1	1	3	5	8	4	1	4	8	4
8	Employee2	2	4	6	7	5	2	4	7	5
janC

Cell Formulas
Range	Formula
M5:DC5	M5	=E1+SEQUENCE(,95,0)
M6:DC6	M6	=E1+SEQUENCE(,95,0)
Dynamic array formulas.

...then what would you need to see or know in a summary or computational area of the worksheet?
You mention that you are looking at calendar months, yet the initial worksheet shown seems to be tabulating by week. And for the cases when one month ends and another begins during the same week, that would present a problem. It is easy to sum the following quantities for a specific date range, including all dates in a specified month:

MrExcel_20240109b.xlsx
	F	G	H	I
6	Total O/T	Working Weekdays	Weekend	Bank Holiday
janC

...but I do not understand how these quantities are to be handled:

MrExcel_20240109b.xlsx
	J	K
6	O/T @ flat rate	O/T @ 1.5
janC

Do you require daily cumulative hours of the "working weekdays" O/T for the week?/month? until some threshold (described somewhere?) is reached, and then subsequent hours beyond that threshold are paid at a different rate? And then when does this augmented rate revert to a normal rate?

 

[harieta]

Hi Kirk,

Apologies is my explanation is all over the place.
I'll try to organise my thoughts a bit better
first column has employee name
second column unique number
third column contract hours
fourth column full time equivalent
from 5th column the calendar starts - it's block of 5 weeks
for payment purpose i only need to see summary of month for flat rate, o/t @ 1.5, o/t @ 1.75 and o/t@2.25. The rest of columns (calculating the overtime by week) can be hidden
The reason for weekly calculation is payment rates depend on the type of contract and type of day (weekday, weekend/Bank Holiday)

Example Marie works 28 hours per week, her FTE is 37 hours / week
Week 1 - she has done 8 hours overtime on Tuesday - because she is not full time employee that 8 hours will be counted @ flat rate
Week 2 she worked overtime on Monday 8 hours and Friday 8 hours - the total is 16 hours, first 9 hours is paid @ flat rate to reach the threshold of full time equivalent, the remaining 7 hours is paid @ 1.5 rate
Week 3 - she worked Good Friday only (Bank Holiday) - that will be paid @ 2.25 irrespective that it is weekday
Week 4 she worked 5 hours on Monday and 8 hours on Saturday - she will be paid 5h @ flat rate and 8 hours @ 1.75
Week 5 - only has 2 days (finishes on Tuesday) - she works on Monday 8 hours - she will be paid for them @ flat rate in next pay run, she picks up another shift on Friday that week for 8 hours - that payment will be split: first 1 hour @ flat rate (because she has already done 8 hours in that week towards full time equivalent and that is the 1 hour that makes it even with full time contract, remaining 7 hours she will be paid @ 1.5 rate

Would this sound a little bit clearer what I'm trying to achieve?

Thank you for all your time

 

[harieta]

I forgot to add, after each calendar month ends and the payment is done, I would lock the tab for that month, so the person who enters the overtime data would not be able to alter anything.

 

[KRice]

Thank you...this is somewhat clearer. You mention being paid "in the next pay run". Are they paid weekly or monthly? In your example, unfortunately, it appears that the end of the week that includes Good Friday (assuming 2024) also happens to coincide with the end of March, so its not clear to me whether you do anything special for a week that includes both the end of one month and the beginning of another. Are the overtime hours tabulated the same in that case, or is something special done to tabulate the hours by week and month both?

Also, you mentioned for Week 5, "finishes on Tuesday". What does that mean? Is that the end of a contract? End of an agreed upon shortened work week?

 

[harieta]

Thank you...this is somewhat clearer. You mention being paid "in the next pay run". Are they paid weekly or monthly? In your example, unfortunately, it appears that the end of the week that includes Good Friday (assuming 2024) also happens to coincide with the end of March, so its not clear to me whether you do anything special for a week that includes both the end of one month and the beginning of another. Are the overtime hours tabulated the same in that case, or is something special done to tabulate the hours by week and month both?

Also, you mentioned for Week 5, "finishes on Tuesday". What does that mean? Is that the end of a contract? End of an agreed upon shortened work week?

pay run is month in arrears, January in paid end of Feb
Last week of March 2024 is calculated the same as other weeks, and there won't be any carry over hours to next month (all weekdays in that last week belong to that month, even though Friday is Bank Holiday). That week will be calculated the same way as others (for transparency and audit ) and then additional columns that has total of all weeks in each month (for visibility those month total calculations can be brought to the beginning of table - before the calendar starts)

the example I gave is when month ends in week 5 on either Monday, Tuesday, Wednesday or Thursday - this is when I cross reference what has been already paid in the payroll run for that month and what needs to be taken into account when calculating 1st week of next month for flat rate and/or 1.5 rate

 

[KRice]

I think perhaps my comment about March 2024 was not clear. I was not asking about how its last week would be handled. March conveniently ends on a Sunday, which is the end of a week. So March "unfortunately" does not serve as a good example for the issue I am asking about. A better example would be February 2024: what happens during its last week, which ends on Sunday, 3 March? The last day of February is Thursday, 29 February. If someone works that Friday, Saturday, or Sunday (all in March), are those hours considered part of same week, or do you actually split the week. I'm not quite clear on this. If I'm understanding the last point in your last post, you do split the week and consider only those days in February for February's pay, but I am assuming that you ignore the month changeover and still consider the full week when determining how many overtime hours get billed at flat/1.5/1.75/2.25 rates...correct?

 

[harieta]

I think perhaps my comment about March 2024 was not clear. I was not asking about how its last week would be handled. March conveniently ends on a Sunday, which is the end of a week. So March "unfortunately" does not serve as a good example for the issue I am asking about. A better example would be February 2024: what happens during its last week, which ends on Sunday, 3 March? The last day of February is Thursday, 29 February. If someone works that Friday, Saturday, or Sunday (all in March), are those hours considered part of same week, or do you actually split the week. I'm not quite clear on this. If I'm understanding the last point in your last post, you do split the week and consider only those days in February for February's pay, but I am assuming that you ignore the month changeover and still consider the full week when determining how many overtime hours get billed at flat/1.5/1.75/2.25 rates...correct?

Hi Kirk,
you are absolutely right.
I will split the week between two months payments
I only need look at hours at weekdays when splitting the month, as the rate is still determined on the whole week as such regardless if it is crossing over from one month to the other
(Saturday and Sunday are always paid @ 1.75, BH are always @ 2.25 and do not count to full time equivalent threshold each week)

 

[KRice]

Great...this fills in a lot of details. I'll have a go at working up an alternative approach to consider/evaluate. One more thing...much of what has been discussed involves part time. Earlier you mentioned that the weekly determination of enhanced pay rates for overtime hours depends on the person's contract status...meaning whether someone is part time or full time. Are the full time rules the same, except there is no "hours offset" to consider, where "hours offset" means the number of hours between contracted hours/week and a full time workload (e.g., Marie has an offset of 9 hours in the earlier example, so the first 9 overtime hours that she works--on regular weekdays--is still billed at flat rate)? I'm guessing that overtime assessments for a full time person are similar except their offset is 0, by definition.

 

[harieta]

yes, full time person would automatically be paid @ 1.5 for weekdays, the rest of the rules is the same, @1.75 for weekend and @2.25 for Bank holidays
thank you

Also, I've tried to replicate the formula for dynamic array, but have come up with #value error, is there a trick i am missing?