Excel formula - If weekend or weekday, then count

Yella Fella · 26 Aug 2022 at 11:59

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.

wesimmo · 26 Aug 2022 at 12:08

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.

Yella Fella · 26 Aug 2022 at 13:27

Thanks, not used the WEEKDAY formulas in conjunction with adding cells up so will have a look at that. Thank you.

Yella Fella · 26 Aug 2022 at 15:19

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.

wesimmo · 26 Aug 2022 at 15:27

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.