Days remaining in month less Sundays.

[Steve Romley]

I need to automatically calculate the # of days remaining in the current
month, minus the total number of remaining Sundays. Any ideas??

 

[JE McGimpsey]

I need to automatically calculate the # of days remaining in the current
month, minus the total number of remaining Sundays. Any ideas??

One way (assuming the date in A1):

This formula needs to be array-entered (CMD-RETURN):

=SUM(DATE(YEAR(A1),MONTH(A1)+1,0), -A1,-(WEEKDAY(A1-1 +
ROW(INDIRECT("1:"&(DATE(YEAR(A1),MONTH(A1)+1,0)-A1)+1)))=7))

 

[JE McGimpsey]

Oops - the previous one gives the wrong values for months ending on
weekends. Try (again, array-entered: CMD-RETURN):

=SUM(DATE(YEAR(A1), MONTH(A1) + 1, 0), -A1, --(WEEKDAY(A1)=1),
-(WEEKDAY(A1 - 1 + ROW(INDIRECT("1:" & (DATE(YEAR(A1), MONTH(A1) + 1,
0)-A1) + 1))) = 1))

 

[Steve Romley]

It works! Thanks

Oops - the previous one gives the wrong values for months ending on
weekends. Try (again, array-entered: CMD-RETURN):

=SUM(DATE(YEAR(A1), MONTH(A1) + 1, 0), -A1, --(WEEKDAY(A1)=1),
-(WEEKDAY(A1 - 1 + ROW(INDIRECT("1:" & (DATE(YEAR(A1), MONTH(A1) + 1,
0)-A1) + 1))) = 1))