If count formula counting hours threshold

[lemmorsojedirdam16]

Hi... I'd like to ask if someone can help me to find the correct formula.
I have an overtime tracking template for 31 days, the threshold total hours for the month is 303 for 30 days which is 10.1 per day, if an employee worked from 1-31, the threshold should retain 303, but if an employee worked let say from 3rd day til 31st day (29 days) the threshold should be 292.9, if 2nd to 31st the threshold will be 303. Thank you... thank you


=IF(COUNT(M10,P10,S10,V10,Y10,AB10,AE10,AH10,AK10,AN10,AQ10,AT10,AW10,AZ10,BC10,BF10,BI10,BL10,BO10,BR10,BU10,BX10,CA10,CD10,CG10,CJ10,CM10,CP10,CS10,CV10,CY10)*(303/31/24=0,"",COUNT(M10,P10,S10,V10,Y10,AB10,AE10,AH10,AK10,AN10,AQ10,AT10,AW10,AZ10,BC10,BF10,BI10,BL10,BO10,BR10,BU10,BX10,CA10,CD10,CG10,CJ10,CM10,CP10,CS10,CV10,CY10)*(303/31)/24)

 

[jorismoerings]

Hi,

Assuming you've got some columns scheduled for the days of the month starting at column M and the cell also contains the daynumber (ic from 1 till 31)
check this and see if it helps:

Book1
	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1	Threshold	1			2			3			4			5			6			7			8
2	292,9				x			x			x			x			x			x			x
Sheet1

Cell Formulas
Range	Formula
L2	=SUMPRODUCT(--(COLUMN($M2:$CY2)=(($M$1:$CY$1)*3+10))*--($M2:$CY2<>""))*(303/30)

 

[lemmorsojedirdam16]

Hi Joris,

Thank you, I tried but it doesnt work on my spreadsheet...

The spreadsheet looks like this...
[TABLE="class: grid, width: 1276"]
<tbody>[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: center"]I
[/TD]
[TD="align: center"]J
[/TD]
[TD="align: center"]K
[/TD]
[TD="align: center"]L
[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]S[/TD]
[TD="align: center"]T[/TD]
[TD="align: center"]U[/TD]
[TD="align: center"]V[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]X[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="colspan: 3, align: center"]DAY 1[/TD]
[TD="colspan: 3, align: center"]DAY 2[/TD]
[TD="colspan: 3, align: center"]DAY 3[/TD]
[TD="colspan: 3, align: center"]DAY 4[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD="align: center"]NAME[/TD]
[TD="align: center"]POS CODE[/TD]
[TD="align: center"]20 DAY[/TD]
[TD="align: center"]TOTAL HOURS WRK[/TD]
[TD="align: center"]POTENTIAL OT - HRS[/TD]
[TD="align: center"]Total Hrs[/TD]
[TD="align: center"]OT[/TD]
[TD="align: center"]OT$[/TD]
[TD="align: center"]Total Hrs[/TD]
[TD="align: center"]OT[/TD]
[TD="align: center"]OT$[/TD]
[TD="align: center"]Total Hrs[/TD]
[TD="align: center"]OT[/TD]
[TD="align: center"]OT$[/TD]
[TD="align: center"]Total Hrs[/TD]
[TD="align: center"]OT[/TD]
[TD="align: center"]OT$[/TD]
[/TR]
[TR]
[TD="align: center"]ROW[/TD]
[TD="align: center"]THRESHOLD[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]ABARENTOS, ROLDAN[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]202:00:00[/TD]
[TD="align: center"]206:34:00[/TD]
[TD="align: center"]4:34[/TD]
[TD="align: center"]10:18[/TD]
[TD="align: center"]0:12[/TD]
[TD="align: center"]$1.10[/TD]
[TD="align: center"]10:24[/TD]
[TD="align: center"]0:18[/TD]
[TD="align: center"]$1.64[/TD]
[TD="align: center"]10:35[/TD]
[TD="align: center"]0:29[/TD]
[TD="align: center"]$2.65[/TD]
[TD="align: center"]10:33[/TD]
[TD="align: center"]0:27[/TD]
[TD="align: center"]$2.47[/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: center"]ABDILAH, RONA[/TD]
[TD="align: center"]BARI[/TD]
[TD="align: center"]40:24:00[/TD]
[TD="align: center"]36:46:00[/TD]
[TD="align: center"]0:00[/TD]
[TD="align: center"]9:12[/TD]
[TD="align: center"]0:00[/TD]
[TD="align: center"]$ -[/TD]
[TD="align: center"]9:32[/TD]
[TD="align: center"]0:00[/TD]
[TD="align: center"]$ -[/TD]
[TD="align: center"]10:02[/TD]
[TD="align: center"]0:00[/TD]
[TD="align: center"]$ -[/TD]
[TD="align: center"]8:00[/TD]
[TD="align: center"]0:00[/TD]
[TD="align: center"]$ -[/TD]
[/TR]
</tbody>[/TABLE]

Let say from Day 1 to Day 20... for 20 days the threshold is 202 which is showing for Roldan, but because Rona worked only until Day 5 her threshold is 40:24 which is also correct. But if another employee started to work from Day 5 until Day 31 (26 days) he/she should have 262.6 threshold which is 10.1*26 days but Roldan if worked 31 days his threshold will remain 10.1*30 which is 303, and not 10.1*31 (313.1)

On J10 the current formula to get the threshold is this=IF(COUNT(M10,P10,S10,V10,Y10,AB10,AE10,AH10,AK10,AN10,AQ10,AT10,AW10,AZ10,BC10,BF10,BI10,BL10,BO10,BR10,BU10,BX10,CA10, CD10,CG10,CJ10,CM10,CP10,CS10,CV10,CY10)*(303/31/24=0,"",COUNT(M10,P10,S10,V10,Y10,AB10,AE10,AH10,AK10,AN10,AQ10,AT10,AW10,AZ10,BC10,BF10,BI10,BL10,BO10,BR10,BU10,BX10,CA10,CD1 0,CG10,CJ10,CM10,CP10,CS10,CV10,CY10)*(303/31)/24)

However, with this formula by the time I fill up the Day 31... the threshold becomes 313.1.
If I change (303/31)/24) to 303/30/24 then Rona will have 252.5 threshold instead of 262.6

 

[jorismoerings]

Hi,

Change the formula to :

Book1
	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1	Threshold	1			2			3			4			5			6			7
2	303	x			x			x			x			x			x			x
Sheet1

Cell Formulas
Range	Formula
L2	=MIN(SUMPRODUCT(--(COLUMN($M2:$CY2)=(($M$1:$CY$1)*3+10))*--($M2:$CY2<>"")),30)*(303/30)