Excel formula - If weekend or weekday, then count

Associate
Joined
18 Apr 2004
Posts
333
Location
Milton Keynes, UK
I have the logic in my head though I can't work out if it's best to go in rows or columns. I've tried both and it adds all.

So I'm trying to count simply what days staff has done overtime working out if it's weekdays or a weekend. I'm using the following format from M3 onwards

Sat | Sun | Mon | Tue | Wed | Sat...
03-Sep | 04-Sep | 05-Sep | 06-Sep | 07-Sep | 10-Sep...
Hours | Hours...

I just enter the number of hours worked in the hour cell. I only want to count up the number of weekdays worked and then the number of weekends worked.


Formula is =IF(AND(M5 > 0,OR(M$3="Sat",M$3="Sun")),COUNTA(M5:BY5),0). But this adds up all cells regardless if it's a weekday or weekend. Do I need to use a loop to work out if it's a particular day of the week AND if it has a number, then add to a list/array and repeat? I'm not sure if it needs to be as complex as it's making out.
 
Soldato
Joined
19 Mar 2012
Posts
6,580
Use the WEEKDAY formula to create a row that turns the date into a value and then COUNTIFS.

Look for non blank cells in the row where the shifts are entered and then where the WEEKDAY result is less than. You ll need to be careful about which WEEKDAY you use.
 
Associate
OP
Joined
18 Apr 2004
Posts
333
Location
Milton Keynes, UK
I tried reading up on weekday but don't really get it. I've fixed it now using this formula... I've set up an array using a named range in which Day is every weekday, and Barry is the name of the person I'm wanting to find out, then replaced with other names in each row. Whether this is more efficient or not, please advise as I'm still learning.

=SUM(COUNTIFS(Day,{"Sat","Sun"},Barry,">0"))

If Barry worked on a Sat or Sun then count the number of Sat and Sun worked across the date range.
 
Soldato
Joined
19 Mar 2012
Posts
6,580
Ha, TBH I hate array formulas...

=WEEKDAY(CELL, #)

I cant remember off the top of my head, but IIRC using 2 as the # means the formula will return 1 for a Monday through to 7 for a Sunday. But if you use a modern excel the tooltip will confirm that.

The one I use most often is to get the WC. Cell - WEEKDAY(Cell, 3).

The 3 returns Monday as 0 through to Sunday as 6, so by taking that away from the original date you get the WC.
 
Back
Top Bottom