CODEWITHSUNDEEP
google-sheets
Alex Ironside
Alex Ironside

Reputation: 5039

count how many specific days are in a time period

I want to count say how many Mondays we have from 2022-02-01 - 2022-03-01. I found smth like this: =SUMPRODUCT(WEEKDAY(B4:C4)=2) - B4 and C4 are the dates But it returns 0. I assume it only checks if specific date is the specific day. Any ideas how I can do this but for a date range? So how count how many Mondays there are in February I also found this

=NETWORKDAYS.INTL(B4;C4;"1000000")

but this returns 25

Upvotes: 2

Views: 1663

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34180

I think this is right. It's counting inclusively so you would get five Mondays starting on Monday 7th Feb 2022 and ended on Monday 7th March 2022 for example.

=floor((B2-(A2+7-weekday(A2,12)))/7)+1

where A2 and B2 contain the start date end end date.

enter image description here

Obvs nul points for me again but for the record this could be generalised if you put the day number in C2 (e.g. 1 if you want to find Sundays, 2 for Mondays):

=floor((B2-(A2+7-weekday(A2,10+C2)))/7)+1

Upvotes: 0

Kessy
Kessy

Reputation: 1994

You can take advantage of the NETWORKDAYS.INTL function by using the string method to make all the days as weekend except for Monday.

The String method states:

weekends can be specified using seven 0’s and 1’s, where the first number in the set represents Monday and the last number is for Sunday. A zero means that the day is a work day, a 1 means that the day is a weekend. For example, “0000011” would mean Saturday and Sunday are weekends.

In this case since you only want to know the Mondays, the string would be "0111111" and the function would look like:

=NETWORKDAYS.INTL(StartDate,EndDate,"0111111")

Upvotes: 4

Related Questions