Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    COUNTIF Weekends (2003 SP3)

    Good morning

    I have a column that identifies weekend days from another column, if overtime is done on weekends the drivers get paid 1.5 times for a Saturday and 2.0 times for a Sunday so I would like to know the amount of weekend overtime has been worked, I have now inserted 2 cells 'Overtime Hours Saturday' and 'Overtime Hours Sunday ' and thought that a COUNTIF function might do it, I have tried =COUNTIF($B$1:$B$31)=WEEKDAY(A1,2)>5 thinking that would add up any numbers (note: Column B is populated by calculations from other cells and will contain either nothing or a number)

    Assuming that this will work and I have just made a stupid mistake somewhere how do you identify Saturday and Sunday seperatley using the Weekday greater than 5 function

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: COUNTIF Weekends (2003 SP3)

    Does this do what you want?

    =SUMPRODUCT((WEEKDAY(A1:A31,2)>5)*B1:B31)

    I have assumed that the dates are in A1:A31.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNTIF Weekends (2003 SP3)

    Thanks Hans

    Thats a great start but I need to seperate the Saturday and Sunday hours, what can I change in this formula to identify those =SUMPRODUCT((WEEKDAY(A1:A31,2)>5)*B1:B31) - To identify the Sunday I realise I can change it to =SUMPRODUCT((WEEKDAY(A1:A31,2)> <font color=red>6</font color=red> )*B1:B31), but I can't seem to identify the Saturday,

    Any ideas please

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: COUNTIF Weekends (2003 SP3)

    To sum the Saturday hours:

    =SUMPRODUCT((WEEKDAY(A1:A31,2)=6)*B1:B31)

    To sum the Sunday hours:

    =SUMPRODUCT((WEEKDAY(A1:A31,2)=7)*B1:B31)

    (1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday, 7 = Sunday)

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: COUNTIF Weekends (2003 SP3)

    Hi Steve

    Try:

    =SUMPRODUCT((WEEKDAY(A1:A31,2)= 6 )*B1:B31)
    Jerry

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNTIF Weekends (2003 SP3)

    Thanks Hans and Jerry

    I struggled with this all day, it is amazing what the eye does not see, because the earlier examples showed > than I assumed it had stayed the same and I had not noticed the subtle change to =, for example =SUMPRODUCT((WEEKDAY(A1:A31,2) <font color=red>=</font color=red> 6)*B1:B31) and I was still using the > sign and pulling my hair out.

    Cheers as usual

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

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