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

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.

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

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))<MO NTH($A$1),A1=""),"",A1+1+(WEEKDAY(A1)=7)))
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Amber" wrote:

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.

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

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

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


"Amber" wrote:

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.

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

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.

"Duke Carey" wrote:

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

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


"Amber" wrote:

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.

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

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

"John C" wrote:

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))<MO NTH($A$1),A1=""),"",A1+1+(WEEKDAY(A1)=7)))
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Amber" wrote:

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.

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

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


"Duke Carey" wrote:

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

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


"Amber" wrote:

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.

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

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 :)
--
** John C **

"Amber" wrote:

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

"John C" wrote:

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))<MO NTH($A$1),A1=""),"",A1+1+(WEEKDAY(A1)=7)))
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Amber" wrote:

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.

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

Pls Try this:

=IF(LEN(A1)=0,"",IF(MOD(A1,7)=0,IF(DAY(A1)<DATE(20 08,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..
--
Kind Regards,
Satti Charvak
Only an Excel Enthusiast


"John C" wrote:

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


"Duke Carey" wrote:

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

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


"Amber" wrote:

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.

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

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.
--
** John C **

"Satti Charvak" wrote:

Pls Try this:

=IF(LEN(A1)=0,"",IF(MOD(A1,7)=0,IF(DAY(A1)<DATE(20 08,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..
--
Kind Regards,
Satti Charvak
Only an Excel Enthusiast


"John C" wrote:

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


"Duke Carey" wrote:

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

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


"Amber" wrote:

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.