| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Fill Series Dates: not letting me change the series from year to m | Excel Discussion (Misc queries) | |||
| I want to use a fill series to copy down a pattern of dates. | Excel Discussion (Misc queries) | |||
| Exclude zeros from series | Charts and Charting in Excel | |||
| How do I create a custom fill series for dates? | Excel Discussion (Misc queries) | |||
| fill a series of dates excluding holidays | Excel Worksheet Functions | |||
Hybrid Mode
