How to count Wednesdays between two dates

Hi J'

This method creates a calendar that extends from the start date to beyond the end date, then uses COUNTIFS to count a specified weekday between those dates.

In the Data Entry table, enter Start Date, End Date and Weekday.

Weekday could be a Pop-Up Menu.

In the Calendar table,

formula in A2 =Data Entry::B1

formula in A3 (and Fill Down) =A2+1

Formula in B2 (and Fill Down) =DAYNAME(WEEKDAY(A2))

(This assumes that Sunday is Day 1 of the week)

Formula in B1 of the Count table =COUNTIFS(Calendar::A,">="&Data Entry::B$1,Calendar::A,"<="&Data Entry::B$2,Calendar::B,"="&Data Entry::B$3)

A quick look at the bottom of the Calendar table to make sure we have sufficient rows to span the start and end dates (I used 1 school term plus a bit):

All good. Sufficient rows to include the end date!

You may want to give the Calendar table extra rows. 366 rows (1 year) should do it!

For a neater appearance, move (cut and paste) the Calendar table to another Sheet. The formulas will still work.

Regards,

Ian.

Hi J',

Thanks for your feedback and the gold star.

I don't know of a way to make Numbers consult the computer's calendar. I find it easier to create a list of dates within a Numbers table and consult that table..

One thing I missed in my reply:

Formula in the Count table A1 =Data Entry::B3

With a Pop-Up Menu in Data Entry B3

That avoids typos 😉

Regards,

Ian.

Ian's approach is easier to understand. But if you don't mind fooling around with slightly longer formulas it turns out you can also accomplish this in Numbers with one formula in one cell.

=INT(DUR2DAYS((WEEKDAY(A2−3)+B2−A2))/7)

For different days of the week, you need to change the A2-3 part.

Sunday A2-1

Monday A2-2

Tuesday A2-3

Wednesday A2-4

etc.

Or you can just use this formula, which will look at the day of the week in C1 and make the appropriate adjustment automatically:

=INT(DUR2DAYS((WEEKDAY(A2−MATCH(C$1,{"Sunday";"Monday";"Tuesday";"Wednesday";"Th ursday";"Friday";"Saturday"},0))+B2−A2))÷7)

SG

should the start and stop date be included in the count?