Count Public Holidays that falls on a Saturday

C

cloud2828

New Member
Joined
Jul 16, 2019
Messages
43
I have a list of public holidays and I would like to know how I could count the ones that fall on a satuday.
 
Sort by date Sort by votes
I'm experiencing a new issue, the list of public holidays has space to enter more data thus those spaces are being counted also, is there a way to prevent this without removing the blank spaces in my table?
 
Upvote 0

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
I'm experiencing a new issue, the list of public holidays has space to enter more data thus those spaces are being counted also, is there a way to prevent this without removing the blank spaces in my table?
Click to expand...
Just check to see if it is also greater than zero (dates are stored as numbers):
=SUMPRODUCT(--(WEEKDAY(A2:A32)=7),--(A2:A32>0))
 
Last edited:
Upvote 0
Joe4 said:
Just check to see if it is also greater than zero (dates are stored as numbers):
=SUMPRODUCT(--(WEEKDAY(A2:A32)=7)--(A2:A32>0))
Click to expand...

This unfortunately didn't work, it still counts the blank spaces. Is this because a blank space is counted as a number?
 
Upvote 0
Also, is it possible to count this for each month of the year?
Click to expand...
Try:
=SUMPRODUCT(--(WEEKDAY(A2:A32)=7),--(A2:A32>0),--(MONTH(A2:A32)=7))
(change the number in red to the month number you want to check for)
 
Last edited:
Upvote 0
This unfortunately didn't work, it still counts the blank spaces. Is this because a blank space is counted as a number?
Click to expand...
I had a typo and missed a comma. I have amended the formula.
 
Upvote 0
Another option
=SUMPRODUCT((WEEKDAY($A$2:$A$31)=7)*($A$2:$A$31>0)*(MONTH($A$2:$A$31)=ROWS($A$1:$A1)))
& drag down to fill 12 cells & it will give the count for successive months
 
Upvote 0
Fluff said:
Another option
=SUMPRODUCT((WEEKDAY($A$2:$A$31)=7)*($A$2:$A$31>0)*(MONTH($A$2:$A$31)=ROWS($A$1:$A1)))
& drag down to fill 12 cells & it will give the count for successive months
Click to expand...

Saw this after I finished manually typing in the months. :LOL:
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Subtracting all calendar days, but end date cannot fall on a weekend or holiday. If it does, the end date shifts to the preceding work day.
Replies
8
Views
223
Romy
R
S
  • Solved
WORKDAY.INTL does not block out holidays
2
Replies
13
Views
540
shawnbull
S
R
  • Question
Calculating future date excluding Public Holiday, Weekends and Backdate specific date
Replies
1
Views
71
etaf
etaf
C
  • Solved
NETWORKDAYS.INTL holiday question
Replies
9
Views
246
Fluff
Fluff
A
  • Solved
count public holiday weekdays in date range
Replies
5
Views
440
ajm
A

Forum statistics

Threads
1,214,827
Messages
6,121,823
Members
449,049
Latest member
cybersurfer5000

Share this page

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