If count formula counting hours threshold

lemmorsojedirdam16

New Member
Joined
Mar 16, 2018
Messages
13
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)
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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
LMNOPQRSTUVWXYZAAABACADAEAFAGAH
1Threshold12345678
2292,9xxxxxxx
Sheet1
Cell Formulas
RangeFormula
L2=SUMPRODUCT(--(COLUMN($M2:$CY2)=(($M$1:$CY$1)*3+10))*--($M2:$CY2<>""))*(303/30)
 
Last edited:
Upvote 0
Hi Joris,

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

The spreadsheet looks like this...


I
J
K
L
MNOPQRSTUVWX






DAY 1DAY 2DAY 3DAY 4

NAMEPOS CODE20 DAYTOTAL HOURS WRKPOTENTIAL OT - HRSTotal HrsOTOT$Total HrsOTOT$Total HrsOTOT$Total HrsOTOT$
ROWTHRESHOLD
10ABARENTOS, ROLDANW202:00:00206:34:004:3410:180:12$1.1010:240:18$1.6410:350:29$2.6510:330:27$2.47
20ABDILAH, RONABARI40:24:0036:46:000:009:120:00$ -9:320:00$ -10:020:00$ -8:000:00$ -

<tbody>
</tbody>

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
 
Upvote 0
Hi,

Change the formula to :


Book1
LMNOPQRSTUVWXYZAAABACADAEAFAG
1Threshold1234567
2303xxxxxxx
Sheet1
Cell Formulas
RangeFormula
L2=MIN(SUMPRODUCT(--(COLUMN($M2:$CY2)=(($M$1:$CY$1)*3+10))*--($M2:$CY2<>"")),30)*(303/30)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top