Count number of each day of the week occur in a certain month

NutritionGal2005 · 02/28/24

I want to type the month/year in a cell and have formulas to tell me how many Sundays, Mondays, Tuesdays etc are in that month. I'm currently entering that manually.

Is there a way?

KPH · 03/02/24

Hi

I do not believe there is a way to count the number of each weekday in a month without creating a sheet that holds all of the dates and that isn't very practical. However, there are logical rules that determine how many of each weekday occur in each month. So, you could develop a series of nested IF functions to return the value based on those rules.

Short Version (TLDR)

Create a column for each weekday as you have.
In row 1 enter the weekday number below each weekday, 1 for Sunday, 2 for Monday, etc.
Add a date formatted column with the first day of the month in it, I call mine Month start date. Steps 1-3 will create the gray cells in the illustration.
In the Sunday column enter the formula below (adjusting it if your column names differ).
Drag this across to the other columns, so the column name Sunday updates. This will populate the white cells in the illustration.

=IF(WEEKDAY($[Month start date]@row) = Sunday$1, IF(NETDAYS($[Month start date]@row, IF(MONTH($[Month start date]@row) = 12, DATE(YEAR($[Month start date]@row), 12, 31), DATE(YEAR($[Month start date]@row), MONTH($[Month start date]@row) + 1, 1) - 1)) >= 29, 5, 4), IF(OR(WEEKDAY($[Month start date]@row) = Sunday$1 + 5, WEEKDAY($[Month start date]@row) = Sunday$1 + 5 - 7), IF(NETDAYS($[Month start date]@row, IF(MONTH($[Month start date]@row) = 12, DATE(YEAR($[Month start date]@row), 12, 31), DATE(YEAR($[Month start date]@row), MONTH($[Month start date]@row) + 1, 1) - 1)) >= 31, 5, 4), IF(OR(WEEKDAY($[Month start date]@row) = Sunday$1 + 6, WEEKDAY($[Month start date]@row) = Sunday$1 + 6 - 7), IF(NETDAYS($[Month start date]@row, IF(MONTH($[Month start date]@row) = 12, DATE(YEAR($[Month start date]@row), 12, 31), DATE(YEAR($[Month start date]@row), MONTH($[Month start date]@row) + 1, 1) - 1)) >= 30, 5, 4), 4)))

Long Version

I have listed below the steps I went through to create this formula so you can understand the process which could be useful for future projects or to make amendments/troubleshoot issues, and have confidence that this is suitable for your needs.

1. Find out if the month starts on the day you are interested in

We are going to use the smartsheet WEEKDAY function that returns a number representing the day of the week, with Sunday being 1. This requires a date format column so we need to add the month start date to our sheet.

This formula will return the message "month starts on Sunday" if the first day of the month is weekday 1, which is Sunday.

=IF(WEEKDAY([Month start date]@row) = 1, "month starts on Sunday")

If we use it in a table like this with the weekday numbers in row 1, instead of the 1 being in the formula (in bold) we can drag this formula to another column later for Monday, Tuesday, etc, without having to edit the formula.

=IF(WEEKDAY([Month start date]@row) = Sunday$1, "month starts on sunday")

And then we can start applying some logic. We know that if a month starts on a Sunday then the next Sunday will be the 8th, then the 15th, then the 22nd, then the 29th. So, that month will have 5 Sundays in it, unless the month is a non-leap February.

So, now we need to find how many days there are in the month.

2. Find the end date of the month

There is not a function to find the last day in the month but you can write a formula using the DATE function, to calculate it. Because months have different lengths (28, 29, 30, 31 days), we need to find the start date of the subsequent month (which is always 1) and subtract one day to find the end date. Like this:

=DATE(YEAR([Month start date]@row), MONTH([Month start date]@row) + 1, 1) - 1

There is a slight complication in that you can't subtract 1 from the month of January to find December. 1-1 is not 12. So we add another IF to return December 31st of the year in the Month start date cell, if the month is December.

=IF(MONTH([Month start date]@row) = 12, DATE(YEAR([Month start date]@row), 12, 31), DATE(YEAR([Month start date]@row), MONTH([Month start date]@row) + 1, 1) - 1)

We don't need to create a column for this but I will for the sake of illustration:

3. Find the Number of Days in the Month

Now that we have the end date, we can find out how many days are in the month, using the NETDAYS function:

=NETDAYS([Month start date]@row, [Month end date]@row)

If we don't want to have a column for Month end date we can use the formula in step 2 within the formula for step 3, like this:

=NETDAYS([Month start date]@row, IF(MONTH([Month start date]@row) = 12, DATE(YEAR([Month start date]@row), 12, 31), DATE(YEAR([Month start date]@row), MONTH([Month start date]@row) + 1, 1) - 1))

4. Count the number of Sundays if the Month starts on Sunday

Now we can return to the formula in step 1 and amend it so that instead of returning the message "month starts on sunday" it can return the count of the Sundays. This formula:

=IF(WEEKDAY([Month start date]@row) = Sunday$1, IF([Days in month]@row >= 29, 5, 4))

This says if the month start date is a Sunday and if there are 29 or more days in the month then there are 5 Sundays. If not there are 4.

Our table now looks like this. I added a row at the top for February 2015 to show how the count would look if the month started on a Sunday and was less than 29 days long.

And because we want to reuse this formula for other weekdays, and don't want to maintain the columns made in steps 2 and 3, I will combine those formula into this one, using the $ to fix the row or cell so we can drag it. The resulting formula looks like this:

=IF(WEEKDAY($[Month start date]@row) = Sunday$1, IF(NETDAYS($[Month start date]@row, IF(MONTH($[Month start date]@row) = 12, DATE(YEAR($[Month start date]@row), 12, 31), DATE(YEAR($[Month start date]@row), MONTH($[Month start date]@row) + 1, 1) - 1)) >= 29, 5, 4))

And will give us this if we drag it across:

Now we have the number of each weekday in a month if the month starts on that day of the week.

Next, we need to find the numbers when the month starts on any of the other 6 days. It gets a little more complex and then very easy.

5. Count the number of Sundays if the month starts on other days (Fridays and Saturdays)

The logic

If the month starts on a Monday, then Sunday will be 7, 14, 21, and 28 there is never a 35th day. So months that start on Monday always have 4 Sundays.
If the month starts on a Tuesday, then Sunday will be 6, 13, 20, 27, and there is never a 34th day. So months that start on Tuesday always have 4 Sundays.
If the month starts on a Wednesday, then Sunday will be 5, 12, 19, 26, and there is never a 33rd day. So months that start on Wednesday always have 4 Sundays.
Thursday follows the same.

So our only concerns are months that start on Fridays or Saturdays.

If the month starts on a Friday, then Sunday will be 3, 10, 17, 24, and 31. So months that start on Friday could have 4 Sundays if they are less than 31 days in the month. Or 5 Sundays if there are 31 days or more.

So we need another IF.

This is just like the formula from step 4 but with two changes shown in bold.

=IF(WEEKDAY($[Month start date]@row) = Sunday$1 + 5, IF(NETDAYS($[Month start date]@row, IF(MONTH($[Month start date]@row) = 12, DATE(YEAR($[Month start date]@row), 12, 31), DATE(YEAR($[Month start date]@row), MONTH($[Month start date]@row) + 1, 1) - 1)) >= 31, 5, 4))

We are now looking at only rows where the month from the month start date is the value in our Sunday cell (which is 1) plus 5, ie 6, which is Friday. So this says: If the month start date is a Friday then evaluate the formula. At the end, we change from >=29 to >=31. Basically, if the month starts on a Friday and has 31 days or more then there are 5 Sundays, if less than 31 days then 4.

If the month starts on a Saturday, then Sunday will be 2, 9, 16, 23, and 30. So months that start on Saturday could have 4 Sundays if they are less than 30 days in the month. Or 5 Sundays if there are 30 days or more.

So make a third version of the IF from step 4.

=IF(WEEKDAY($[Month start date]@row) = Sunday$1 + 6, IF(NETDAYS($[Month start date]@row, IF(MONTH($[Month start date]@row) = 12, DATE(YEAR($[Month start date]@row), 12, 31), DATE(YEAR($[Month start date]@row), MONTH($[Month start date]@row) + 1, 1) - 1)) >= 30, 5, 4))

I don't know how experienced you are with formula and realize this might be getting confusing now, but we are almost there. Here is how it would look if the formula that counts Sundays in months that start on a Friday was in Sunday part 2 and the formula that counts Sundays in months that start on a Saturday was in Sunday part 3. The one in the original Sunday column is from step 4 and counts the Sundays for months that start on a Sunday.

6. Adjust this so we can count other days using the same formula

We now have a formula that can find the number of Sundays in months that start on Sundays, Fridays, and Saturdays. To make sure this can be reused in other columns for every day of the week we can't keep the +5 and +6 in the formula as they are. This works when we are looking for Sunday as 1+5 is 6 and 1+6 is 7. But, if we want to look for Mondays then 2+5 is 7 and 2+6 is 8 which will not work. We can use an OR. If the Month starts on 2 plus x or 2 plus x minus 7. So Mondays would be 2 + 6 or 2 + 6 - 7, ie 8 (which is not a day) or 1, which is Sunday, and the day we want.

This

=IF(WEEKDAY($[Month start date]@row) = Sunday$1 + 6,

becomes

=IF(OR(WEEKDAY($[Month start date]@row) = Sunday$1 + 6, WEEKDAY($[Month start date]@row) = Sunday$1 + 6 - 7)

I think this is the hardest part to explain and might revisit my explanation later.

7. Combine formula to count the number of Sundays if the month starts on other days (Fridays, Saturdays, and Sundays)

We can nest the IF functions from steps 4 and 5.

=IF(WEEKDAY($[Month start date]@row) = Sunday$1, IF(NETDAYS($[Month start date]@row, IF(MONTH($[Month start date]@row) = 12, DATE(YEAR($[Month start date]@row), 12, 31), DATE(YEAR($[Month start date]@row), MONTH($[Month start date]@row) + 1, 1) - 1)) >= 29, 5, 4),

IF(OR(WEEKDAY($[Month start date]@row) = Sunday$1 + 5, WEEKDAY($[Month start date]@row) = Sunday$1 + 5 - 7), IF(NETDAYS($[Month start date]@row, IF(MONTH($[Month start date]@row) = 12, DATE(YEAR($[Month start date]@row), 12, 31), DATE(YEAR($[Month start date]@row), MONTH($[Month start date]@row) + 1, 1) - 1)) >= 31, 5, 4),

IF(OR(WEEKDAY($[Month start date]@row) = Sunday$1 + 6, WEEKDAY($[Month start date]@row) = Sunday$1 + 6 - 7), IF(NETDAYS($[Month start date]@row, IF(MONTH($[Month start date]@row) = 12, DATE(YEAR($[Month start date]@row), 12, 31), DATE(YEAR($[Month start date]@row), MONTH($[Month start date]@row) + 1, 1) - 1)) >= 30, 5, 4),

)))

The sheet will now look like this:

8. Add logic for other weekday starts (Monday-Thursday)

We know from the logic in step 4 (the bulleted list) that if the month starts on a Monday, Tuesday, Wednesday, or Thursday, it will always have 4 Sundays. So we can add that to our IF. The change is the addition of a 4 at the very end - if the other IFs are not true (which previously left a blank cell), now a 4 is returned.