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)
Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.Don´t forget to add my
April 30th, 2008 at 5:59 pm
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?
April 30th, 2008 at 6:26 pm
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
November 22nd, 2011 at 4:31 pm
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.
November 25th, 2011 at 9:12 am
Steve,
read this post:
How many of a specific weekday falls between a start date and an end date except holidays