# Determine number of non-Sundays in a part of a month

Calculate number of Sundays in the month asks how many Sundays there are to the end of the month.  Two users proposed very similar answers that I liked and worked very well to determine how many Sundays to the end of the month.  I'm looking for how many days without Sundays remain in a given month – this seems to be not exactly the same question – and I'm stuck.

What I'm using up to now, modifying this formula to `A1` on what would be the cell with the current month's first of the month (and each month's for each column), is:

``````=DAY(EOMONTH(A1,0))-DAY(TODAY()-1)-(SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(A1&":"&DATE(YEAR(A1),MONTH(A1),DAY(TODAY()-1)))))=1))-1)
``````

(Total number of days in the month) - (today plus one to include today) - (minus the Sundays to today per the proposed solutions to the other question, minus 1 to exclude today if it's a Sunday), and that is where it breaks. I arbitrarily added the last `-1` (which in the parentheses it comes out as adding 1) but it really doesn't work.

I have a table with daily sales for the year, the daily average for each month and with the remaining days to the end of the month × the daily average, what the possible expected monthly sale would be. I include the current day on the Days Remaining because if today is a working day, there are sales, but I should exclude it if today is a Sunday, as we don't work on Sundays.

Is there a way to correctly determine, from a given day of the month included, how many days without Sundays remain in a given month? Maybe there is a better, more elegant way and I should be using a completely different formula :)

• (1) I don’t understand why there is an `A1` in the formula.  If you want to determine information about the current month, you can determine the month by looking at `TODAY()`.  (2) There’s very little point in posting a screenshot of a spreadsheet with the numbers blocked out.  The fact that you show the numbers in Row 42 and don’t explain what they are adds insult to injury.  (3) The fact that you don’t show `A1` is also bad.  … (Cont’d) – Scott Mar 24 at 14:36
• (Cont’d) …  (4) What you need to do is show us what results you want for what input. I guess you’re looking for a number. What value do you want today (24-Mar-19, which is a Sunday)? What value do you want tomorrow? Give a table of results (as text, not an image) with explanations. – Scott Mar 24 at 14:36
• I can't make sense of the embedded table. Also, what you're asking isn't clear. In the first paragraph, you say you aren't interested in the number of Sundays to the end of the month, you want the number of Sundays to date. The last paragraph says you want the number of days excluding Sundays remaining in the month. Please give a clear statement of the result you need. – fixer1234 Mar 24 at 15:29
• Hi Scott, (1) it's not just the current month, but every month, and on each column I will put this formula, so A1 is the cell reference for each one. (2) the shown numbers are the remaining days to the end of the month, and I want those to be the remaining days without the Sundays, that row are the cells for this formula. (3) A1 is the reference for the first of the month for each column. (4) I'm looking for the number of days remaining to the end of the month, without Sundays. For example today 24 March '19, there are six remaining days, that's the number. Thanks – lucioh Mar 24 at 17:31
• Hi fixer1234, you're right, my mistake in writing. The end result would be how many days are left in the month, from today, subtracting Sundays. I hope it is clearer. Thanks. – lucioh Mar 24 at 17:32

``````=SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(A1&":"&EOMONTH(A1,0))))=1))
gives the number of Sundays between the date in `A1` and the last day of the month of `A1`, inclusive.  This question is asking for the number of non-Sundays between `A1` and the last day of the month of `A1`, inclusive.  It’s a simple matter of changing `=` to `<>` (not equal):
``````=SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(A1&":"&EOMONTH(A1,0))))<>1))
• I’m glad I got you to a solution, but (1) the formula you posted gets an error; it has unmatched parentheses.  (Adding `)` to the end makes it not fail.) (2) I have no idea what you’re actually doing. – Scott Mar 24 at 19:38