9

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

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.

  • 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 Dec 24 '14 at 4:16
  • yup exactly.. :) – Jay-ar Dec 24 '14 at 10:36
9

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. – wbeard52 Jun 19 '14 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))
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.

  • What is Networkdays.Intl it does not work with all excel – yass Jul 1 '17 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 '17 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 Dec 3 '19 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

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"))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.