Week Number/Month/Year Assignment Questions

[AnnRanger]

Hi All,

Just started a new job and things have been going well until now. I been given an assignment in excel and cannot figure out how to do it. Up until now, I am a intermediate excel user, know the basic functions including pivot tables.

Here is what I am trying to do:

I have 3,650 records in an excel table. Each of the records represents a task to be completed starting 1/1/2018. 70 tasks will be completed per week.

1) I want to add a column that assigns a week# to each block of 70 records, (e.g., First 70 records all have week#1 associated with them, next 70 records all have week#2 associated, etc.)

2) Then I want to add another column that identifies the month and year for each of the records, based on the week assigned to that record.

3) Finally, how can the month assignment take into account that for a week that straddles two months, some of the 70 records will fall in one month and the remainder of the 70 records will fall in the next month (e.g., of the 70 records assigned to week 5, 40 of those records will be in January and the remaining 30 records will be in Feb.)

Any help will be VERY much appreciated. Thanks in advance.

Ann

 

[BrianJN1]

Let's see if we can get some dialog on this.
Sure, this table is NOT as what you see but rather a summation of what I anticipate that you desire:

Records	Wk Assign End Wk	Month
1-70	Wk1	8/01/2018	Jan-18
71-140	Wk2	15/01/2018	Jan-18
141-210	Wk3	22/01/2018	Jan-18
211-280	Wk4	29/01/2018	Jan-18
281-350	Wk5	5/02/2018	Feb-18
351-420	Wk6	12/02/2018	Feb-18

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>

I have "batches" of 70 records each assigned with a week number and its ending date. The month has been assigned using the mmmm-yy custom format.
Where do you need to take this from here?