Excel 2007 : Start and End of 52 Weeks for 2009?

**binar** · 02-01-2009, 08:21 PM

Fellow Forum Members,

How can I make Excel automatically autofill "column A" with 52 weeks for the entire year of 2009 as shown below.

2/1/2009 - 2/7/2009
2/8/2009 - 2/14/2009
2/15/2009 - 2/21/2009
2/22/2009 - 2/28/2009 and so on to Dec 2009.

The week starts on Sunday (i.e 2/1/2009) and ends on Saturday (i.e. 2/7/2009) and both dates need to be in one cell.
Any help greatly appreciated. Thanks.

**shg** · 02-01-2009, 08:29 PM

In some cell, put the start date (I used C1) and in A1 and copy down,

=TEXT($C$1 + 7 * (ROW() - ROW(A$1)), "mm/dd/yyyy - ") & TEXT($C$1 + 7 * (ROW() - ROW(A$1)) + 6, "mm/dd/yyyy")

Change ROW(A$1) to reference the first cell in which the formula appears.

**binar** · 02-01-2009, 09:35 PM

Originally Posted by shg

In some cell, put the start date (I used C1) and in A1 and copy down,

=TEXT($C$1 + 7 * (ROW() - ROW(A$1)), "mm/dd/yyyy - ") & TEXT($C$1 + 7 * (ROW() - ROW(A$1)) + 6, "mm/dd/yyyy")

Change ROW(A$1) to reference the first cell in which the formula appears.

Shg,
Thanks again. You helped me get out of another tight spot I was in because I couldn't find any post from the acrhive that matched what I wanted to do and Google wasn't any help either.

I checked all the dates your formula automatically generates on a filldown against my 2009 calendar and all the dates match perfectly for the beginning and ending of each week ! Very impressive. I have to say you are genius.

Moreover, I don't understand what you mean by:

"Change ROW(A$1) to reference the first cell in which the formula appears. "

I'm not too clear why I need to do this since everything seems to look okay to me. Attached is my file with your formula. If you see a problem please let me know. Thanks.

**shg** · 02-01-2009, 09:43 PM

Moreover, I don't understand what you mean by:

If the formula starts in A5 instead of A1, change ROW(A$1) to ROW(A$5).

If that sorts it for you, would you please mark the thread as Solved?

Click the Edit button on your first post in the thread

Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes

**binar** · 02-01-2009, 10:11 PM

Originally Posted by shg

If the formula starts in A5 instead of A1, change ROW(A$1) to ROW(A$5).

If that sorts it for you, would you please mark the thread as Solved?

Click the Edit button on your first post in the thread

Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes

Shg,
Thanks. That sorts it out.