10

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

Improve this question

6 Answers 6

Reset to default
13

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.

Improve this answer
2
  • 1
    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?
    – wbeard52
    Commented Dec 24, 2014 at 4:16
  • yup exactly.. :)
    – Jay-ar
    Commented Dec 24, 2014 at 10:36
11

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)
Improve this answer
1
  • Simple. Very elegant compared to my formula above. Thanks.
    – wbeard52
    Commented Jun 19, 2014 at 13:20
3

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

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

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

Improve this answer
3
  • What is Networkdays.Intl it does not work with all excel
    – yass
    Commented 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
    Commented 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
    – phuclv
    Commented Dec 3, 2019 at 10:35
1

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

Improve this answer
1

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"))
Improve this answer
1
  • TO get first day of month you can do this: =EOMONTH(A1,-1)+1
    – Eneas Gesing
    Commented Jun 13, 2023 at 12:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .