Results 1 to 4 of 4

Thread: If count formula counting hours threshold
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2018
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question If count formula counting hours threshold

    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,CD1 0,CG10,CJ10,CM10,CP10,CS10,CV10,CY10)*(303/31)/24)

  2. #2
    Board Regular
    Join Date
    Jul 2014
    Location
    The Netherlands
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: If count formula counting hours threshold

    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:
    LMNOPQRSTUVWXYZAAABACADAEAFAGAH
    1Threshold12345678
    2292,9xxxxxxx

    Sheet1



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

    Last edited by jorismoerings; Aug 22nd, 2019 at 01:35 AM.
    --------------------------------------------------------------------------------
    If you found my answer helpful, please hit the Like or Thank button.

    Please follow the forum Rules and Guidelines and use Code tags around your VBA code.

    Remember: test VBA code always on a copy of your file because usually you can't use <undo>

  3. #3
    New Member
    Join Date
    Mar 2018
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Re: If count formula counting hours threshold

    Hi Joris,

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

    The spreadsheet looks like this...


    I
    J
    K
    L
    M N O P Q R S T U V W X






    DAY 1 DAY 2 DAY 3 DAY 4

    NAME POS CODE 20 DAY TOTAL HOURS WRK POTENTIAL OT - HRS Total Hrs OT OT$ Total Hrs OT OT$ Total Hrs OT OT$ Total Hrs OT OT$
    ROW THRESHOLD
    10 ABARENTOS, ROLDAN W 202:00:00 206:34:00 4:34 10:18 0:12 $1.10 10:24 0:18 $1.64 10:35 0:29 $2.65 10:33 0:27 $2.47
    20 ABDILAH, RONA BARI 40:24:00 36:46:00 0:00 9:12 0:00 $ - 9:32 0:00 $ - 10:02 0:00 $ - 8:00 0:00 $ -

    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

  4. #4
    Board Regular
    Join Date
    Jul 2014
    Location
    The Netherlands
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: If count formula counting hours threshold

    Hi,

    Change the formula to :

    LMNOPQRSTUVWXYZAAABACADAEAFAG
    1Threshold1234567
    2303xxxxxxx

    Sheet1



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

    --------------------------------------------------------------------------------
    If you found my answer helpful, please hit the Like or Thank button.

    Please follow the forum Rules and Guidelines and use Code tags around your VBA code.

    Remember: test VBA code always on a copy of your file because usually you can't use <undo>

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •