1

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
0

user743733’s answer to Calculate number of Sundays in the month,

=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))
  • Thanks!, this worked. The final formula is: =DAY(EOMONTH(A1,0)-DAY(TODAY()-1)-SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(DATE(YEAR(A1),MONTH(A1),DAY(TODAY()))&":"&EOMONTH(A1,0))))=1)) I write this formula on each monthly column with A1, B1, E1, etc. as 1/1/19, 2/1/19, 3/1/19, etc. On the result, I get, for example on the 24th of the month, 7, 4, 6, respectively. Thank you! – lucioh Mar 24 at 19:28
  • 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
  • (1) I see where I missed it, actually it is after the first eomonth, to close the DAY function, it should be: =DAY(EOMONTH(A1,0))-DAY(TODAY()-1)-SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(DATE(YEAR(A1),MONTH(A1),DAY(TODAY()))&":"&EOMONTH(A1,0))))=1)) (2) it allows me to determine remaining working days in a month (Mon-Sat) and multiply those by average daily sale in the previous days same month, giving me what I can expect to sell that month to project results. I've been using it for several years and it is quite accurate, recently we started to close on Sundays and I was getting inaccurate results – lucioh Mar 24 at 19:46

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.