# Calculate number of Sundays in the month

In Microsoft Excel I am looking to determine the number of Sundays in any given month. I am putting together a budget spreadsheet and some of the sub accounts get extra funding when five Sundays are in the month instead of four.

The only cell I have to reference is the title of the sheet which is in Cell A1 and equals the current month. For Example `6/1/14`

Try this where `A1` is the starting day of the month:

``````=SUMPRODUCT(N(TEXT(ROW(INDIRECT(A1&":"&EOMONTH(A1,0))),"ddd")="Sun"))
``````

You can also use this for any day of the week just change `"Sun"` and any date range by replacing `A1` with start date and `EOMONTH(A1,0)` with end date.

• Wow. This is really creative. Let me understand. We create the date values for the whole month with the indirect and row functions? We then convert them to text in the form of Sun, Mon, etc. We evaluate for the condition of Sun and change the result to a one or a zero and then sum the result. Is that correct? Dec 24, 2014 at 4:16
• yup exactly.. :) Dec 24, 2014 at 10:36

If the date you mention is always the first of the month, then this one will also work:

``````=INT((WEEKDAY(A1-1)+EOMONTH(A1,0)-A1)/7)
``````
• Simple. Very elegant compared to my formula above. Thanks. Jun 19, 2014 at 13:20

Very similar to a previous answer, but just a little bit cleaner...

``````=SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(A1&":"&EOMONTH(A1,0))))=1))
``````
``````=DAY(EOMONTH(A1,0))-NETWORKDAYS.INTL(A1,EOMONTH(A1,0),11)
``````

EDIT: This works in Excel 2016, not sure when NETWORKDAYS.INTL was introduced.

• What is Networkdays.Intl it does not work with all excel
– yass
Jul 1, 2017 at 18:45
• Ah, you are right. I did not see he was referring to Excel 2010. I have 2016. Unlike networkdays, Networkdays.intl lets you pick the weekends. In this case, I told Excel that the weekend is Sunday only, that's what the 11 stands for. So it gives you the number of days in a period not counting Sundays.
– SRCP
Jul 1, 2017 at 19:47
• `NETWORKDAYS.INTL` is available since Excel 2010. You can see the number 2010 beside its name. see screenshot of it working in Excel 2010 Dec 3, 2019 at 10:35

I believe I've found yet another way to skin this cat (so to speak).

If, you want to avoid entering dates, and have a large sheet for projection (18 months out for example) then you could instead just tell the formula what your month and year are, and have it formatted accordingly. This joined with a absolute reference to a header cells which contains the day of the week in question and you have a much more compact and cleaner table (in my opinion).

A3 = Jan, B3= 2017, C2:I2= 3 Char Day Abbreviation (i.e. SUN)

Enter formula into cell C3

``````=SUMPRODUCT(N(TEXT(ROW(INDIRECT(DATEVALUE(TEXT(CONCAT(\$A3,"/",\$B3),"mm/dd/yyyy"))&":"&EOMONTH(DATEVALUE(TEXT(CONCAT(\$A3,"/",\$B3),"mm/dd/yyyy")),0))),"ddd")=C\$2))
``````

Happy Hunting

A slight variation of the accepted answer, where you don't need the first day of the month in `A1`.

This is achieved by replacing `A1` with:

``````date(year(today()),month(today()),1)
``````

This is assuming that you want it for the current month.

``````=SUMPRODUCT(N(TEXT(ROW(INDIRECT(date(year(today()),month(today()),1)&":"&EOMONTH(date(year(today()),month(today()),1),0))),"ddd")="Sun"))
``````