RapidFireGT
New Member
- Joined
- Mar 25, 2008
- Messages
- 26
I have a spreadsheet showing a variety of inventory items (rows) and each item’s stock count for each week (columns). The spreadsheet should looks like this:
Each week’s date range spans 5 days (Monday – Friday). I would like to keep the most recent week as the left most column. Each week, a new column will be inserted in Column “C” and will have the inventory stock levels for the past week. Therefore, if we have had 5 weeks in the year so far, Column “C” will be Week 5, Column “D” will be Week 4, Column “E” will be Week 3, and so on.
When I insert a new column into Column “C”, I want to drag the date range formula from Column “D” to the new Column “C,” and it should auto-fill column “C” with the following week’s date range. My current formula is auto-filling the end-of-the-week date, but not the beginning-of-the-week date. For example, say our starting date is the first week of October. This week is Oct. 01 – Oct. 05.
Here is the original date:
Here are the dates when I insert a new column at Coloumn “C”, and then auto-fill the date for Column “C” by dragging the formula from Column “D”:
As you can see, the ending dates are properly calculating (Oct. 12 for Week 2 and Oct. 5 for Week 1), although the beginning dates for these weeks are not properly calculating.
Here is the formula for the original Column C:
Can anyone please correct my formula so that the beginning-of-the-week date also calculates properly?
Thanks guys!
Each week’s date range spans 5 days (Monday – Friday). I would like to keep the most recent week as the left most column. Each week, a new column will be inserted in Column “C” and will have the inventory stock levels for the past week. Therefore, if we have had 5 weeks in the year so far, Column “C” will be Week 5, Column “D” will be Week 4, Column “E” will be Week 3, and so on.
When I insert a new column into Column “C”, I want to drag the date range formula from Column “D” to the new Column “C,” and it should auto-fill column “C” with the following week’s date range. My current formula is auto-filling the end-of-the-week date, but not the beginning-of-the-week date. For example, say our starting date is the first week of October. This week is Oct. 01 – Oct. 05.
Here is the original date:
Here are the dates when I insert a new column at Coloumn “C”, and then auto-fill the date for Column “C” by dragging the formula from Column “D”:
As you can see, the ending dates are properly calculating (Oct. 12 for Week 2 and Oct. 5 for Week 1), although the beginning dates for these weeks are not properly calculating.
Here is the formula for the original Column C:
Code:
=TEXT(DATE(2012,9,24)+(COLUMN($C:C)-3)*(-7)+7,"mmm dd, 'yy")&" - "&TEXT(DATE(2012,10,1)+(COLUMNS($C:C)-3)*7+18,"mmm dd, 'yy")Can anyone please correct my formula so that the beginning-of-the-week date also calculates properly?
Thanks guys!
