How to exclude dates which equal SUNDAY from a fill series?

[Amber]

I want to fill a series of dates, for example cell A1 = November 1, 2008
through cell A30 = November 30, 2008. BUT I do not want to include any
Sundays, so A1 = Saturday 11/1, cell A2 = Monday 11/3, etc. I also want to
only fill one month, so if the end of the list goes to December, that would
be excluded also. (I think I figured that one out but if it conflicts I will
need help) Thank you so much.

 

[John C]

With the date entered into A1, the first of whatever month, type the
following into A2, and copy down to A30
=IF(A1="","",IF(OR(MONTH(A1+1+(WEEKDAY(A1)=7))<>MONTH($A$1),A1=""),"",A1+1+(WEEKDAY(A1)=7)))

 

[Duke Carey]

To skip Sundays: with 11/1/08 in A1, enter this in A2 and copy down

=IF(WEEKDAY(A1)<7,1,2)+A1

 

[Amber]

This worked to exclude Sundays, thank you. The more complicated formula from
John C worked to exclude Sundays AND also excluded dates that went into the
next month.

 

[John C]

What value do you get in A26?
--
** John C **

To skip Sundays: with 11/1/08 in A1, enter this in A2 and copy down

=IF(WEEKDAY(A1)<7,1,2)+A1

 

[Amber]

This worked to exclude Sundays AND exclude the following month. Thank you!

 

[John C]

Thank you for the feedback. And just as an FYI, you will never need the
entire range of 30 cells from A1:A30 assuming you always want to exclude
Sundays. The most number of days you will ever have in a month excluding
Sundays is 27. So you could just use the formula from A2:A27, and you get 3
extra rows to play with

 

[Satti Charvak]

Pls Try this:

=IF(LEN(A1)=0,"",IF(MOD(A1,7)=0,IF(DAY(A1)<DATE(2008,MONTH($A$1)+1,1)-DATE(2008,MONTH($A$1),1)-1,A1+2,""),IF(DAY(A1)<DATE(2008,MONTH($A$1)+1,1)-DATE(2008,MONTH($A$1),1),A1+1,"")))

It will work for all months..

 

[John C]

My formula already worked in an earlier post, and is quite a bit shorter. I
was asking Duke about his value in A26, because his formula did not omit
dates for the 'next' month.