Excel: Count the number of a specific weekday in a range
Filed in Excel on Oct.22, 2007. Email This article to a Friend
Question: How to count, for example, mondays in a range?
Answer: If your range looks like this (see picture below), use =SUM(N(WEEKDAY(A1:A15)=2)) + Ctrl +Shift Enter
If your range looks like this (see picture below), use =SUM(N(WEEKDAY(ROW(INDIRECT("D4:D"&A18-A17+1))+A17-1)=4)) + Ctrl +Shift Enter
This formula creates all dates in the range and checks how are a match to 4. (Wednesday)
Related posts:
How many of a specific weekday falls between a start date and an end date except holidays
Count specific weekdays in a date range
Excel: Count the number of cells within a range that meet the given condition
Sort numeric values by closest to farthest to a specific number in excel (array formula)
Recommended categories
Hi there, it seems very helpful and it works. however, i would like the range to be adapted to possible changes.
what i mean is that sometimes i would want it to start fron cell a2 and finish at call a19.
is there a way to make this connection?
aqctually i think it's better if i give you an exact description.
I am trying to make a sheet that calculates the total amount of money that has to be paid as "Easter present" to an employee. This amount depends on the day the employ started working, the day they finished and the total number of sundays between these days.
so, an employ might have started at Jan 1 and finished at April 30 (that's the maximum period). another employ might have started at feb 15 and finished at april 25.
what i'm trying to do is to be able to put those dates in 2 seperate cells, so as the given function to be able to take those dates as starting and finishing point of the range (as it looks on the 1st picture).
thanks a lot
Right now I'm using the following formula to tell me how many of a specific defined day, ie Wednessdays,fall between a start date and stop date. Here is the formula:
=SUMPRODUCT(--(ISNUMBER(MATCH(TEXT(ROW(INDIRECT(D3&":"&D4)),"dddd"),D15,0))))
The only thing is this doesn't take into account sepcific holidays that occur during the period that i dont want to include in the count. How can i adjust this so i can not include defined holidays.
Steve,
read this post:
How many of a specific weekday falls between a start date and an end date except holidays