How to display list of dates for the year excluding Sundays

[KitLam]

Hello,
I would like to use a formula to take the starting date in cell A1 and then have Excel give me a list of all the dates for 2009 excluding Sundays.

So A1 would be 1/01/09

Then Excel would list
Thursday, Jan 01
Friday, Jan 02
Saturday, Jan 03
Monday, Jan 05
Tuesday, Jan 06
etc....

I've tried messing with the WEEKDAY function, but can't seem to figure it out.

Thanks in advance for the help!

 

[Armando Montes]

Hi, try this:
=IF(WEEKDAY(A1)<>7,1,2)+A1

 

[KitLam]

Hi Armando Montes,

Thanks for the quick reply! Could you give me a bit more instruction on how to use that?
I put that formula in A3 and got Friday, January 02, 2009 - now how do I fill in the rest of the dates for 2009? If I use the black + sign and fill down it gives me the same date but used 1900 for the year.

 

[sanrv1f]

try

Excel Workbook
	A
1	1-Jan-09
2	2-Jan-09
3	3-Jan-09
4	5-Jan-09
5	6-Jan-09
6	7-Jan-09
Sheet1

Cell Formulas
Range	Formula
A2	=A1 +1 +(WEEKDAY(A1) =7)
A3	=A2 +1 +(WEEKDAY(A2) =7)
A4	=A3 +1 +(WEEKDAY(A3) =7)
A5	=A4 +1 +(WEEKDAY(A4) =7)
A6	=A5 +1 +(WEEKDAY(A5) =7)

 

[KitLam]

Hello Sanrv1f,

Thank you! Worked perfectly. This will help us run our small milk delivery company work more efficient. I appreciate your time.

 

[KitLam]

As a bonus, it would be nice if it would automatically leave a row blank where Sunday would be when I'm populating the column with the dates. This might be more complicated than it is worth. Basically I'm going to go in and put in a row to do subtotals for each week and having the blank line just saves me a step.

Thanks!

 

[tusharm]

In A2 (leaving A1 alone) enter a legit non-Sunday date.

Then, in A3 enter the formula =IF(A2="",A1+2,IF(WEEKDAY(A2)=7,"",A2+1))

Copy A3 as far down as needed.

As a bonus, it would be nice if it would automatically leave a row blank where Sunday would be when I'm populating the column with the dates. This might be more complicated than it is worth. Basically I'm going to go in and put in a row to do subtotals for each week and having the blank line just saves me a step.

Thanks!

 

[KitLam]

Tushar,

It is truly amazing what Excel can do and how great you all are for sharing. Say I wanted to automate the process of now going in on every blank row that was created by using the code above and highlight the blank row pink, and put the word Subtotal in column A, and then total the rows above it for each week. So column BCDEFGHIJK and L would be subtotaled on the blank line for the week.

Basically I would just create a row and copy and paste it on each of the blank lines, but I'm curious to know if there is an easier way.

Thanks again everyone.

Kit

 

[tusharm]

Thank you for your comments and recognition of the effort people put into sharing their expertise. You should keep that in mind when asking incremental questions instead of asking what you *really* want done. With incremental questions, you guarantee that the time and effort of the volunteers yields results that you will simply throw away!

I would solve your problem rather differently than your approach. Add a new column 'WeekOf' with the formula
=A2-WEEKDAY(A2)+1 -- assuming col. A contains the date.

Now, create a PivotTable with WeekOf as the row field, and the different quantities you want to measure as the data fields. Sort the WeekOf ascending and ensure that the data fields are all set to 'Sum Of' (or if you prefer another metric such as Average, use that).

I used the above method as a building block to help a large software company develop metrics to measure and track the development pace of one of their key projects.

Tushar,

It is truly amazing what Excel can do and how great you all are for sharing. Say I wanted to automate the process of now going in on every blank row that was created by using the code above and highlight the blank row pink, and put the word Subtotal in column A, and then total the rows above it for each week. So column BCDEFGHIJK and L would be subtotaled on the blank line for the week.

Basically I would just create a row and copy and paste it on each of the blank lines, but I'm curious to know if there is an easier way.

Thanks again everyone.

Kit

 

[KitLam]

Hi,

I guess the incremental questions were really an after thought, because I didn't think any of that was possible to accomplish easily (without a lot of VB coding) I had planned to do it manually. I have applied hundreds of tips to my spreadsheets by searching this forum and I do keep a file with all of the tips I've learned from volunteers here so that I can use them on future projects, so please don't think that any results get thrown away. There have been several times I've used solutions on other sheets.

In the future I'll try to give a better picture of the whole project before I post any questions. Thanks again for your assistance!