EXCEL VERSION
Excel 2013 (15.0.4631.1000)
BACK STORY
I create weekly reports, reporting activity based on a 'Week number' defined by a custom year. Custom year = 01 SEP - 31 AUG. Each week within this custom year is defined by a week per row in a named table. E.g. Monday 02/09/2013- Sunday 08/09/2013. The row lists the week start date (Monday), end (Sunday) and defined 'Week number'.
As I have many worksheets with various data sets, all data has to have a column = week number.
WHAT I'VE DONE SO FAR
I've seen 2 approaches to this. One is to use a modular formula, the other using the following INDEX and nested IF formulas.
For example:
=INDEX(B3,MATCH(A6:C10),IF((A3)>(A6:A10)),IF((A3)<(B6:B10)),1,0)
WHERE THE CHALLENGE LIES
I am trying to figure out a modulo (or index) formula where the following user entry of a date automatically populates the corresponding 'week number'.
It is apparent I've made a botch job of the above attempt. As a result, it'd be great to get some assistance if possible!
EXAMPLE DATASET
<tbody>
</tbody>
Excel 2013 (15.0.4631.1000)
BACK STORY
I create weekly reports, reporting activity based on a 'Week number' defined by a custom year. Custom year = 01 SEP - 31 AUG. Each week within this custom year is defined by a week per row in a named table. E.g. Monday 02/09/2013- Sunday 08/09/2013. The row lists the week start date (Monday), end (Sunday) and defined 'Week number'.
As I have many worksheets with various data sets, all data has to have a column = week number.
WHAT I'VE DONE SO FAR
I've seen 2 approaches to this. One is to use a modular formula, the other using the following INDEX and nested IF formulas.
For example:
=INDEX(B3,MATCH(A6:C10),IF((A3)>(A6:A10)),IF((A3)<(B6:B10)),1,0)
WHERE THE CHALLENGE LIES
I am trying to figure out a modulo (or index) formula where the following user entry of a date automatically populates the corresponding 'week number'.
It is apparent I've made a botch job of the above attempt. As a result, it'd be great to get some assistance if possible!
EXAMPLE DATASET
1 | A | B | C |
---|---|---|---|
2 | USER ENTRY DATE | WK | |
3 | 12/09/2013 | <formula result="" e.g="" wk="" no="2"></formula> | |
5 | |||
6 | WK START | WK END | WK NO |
7 | 02/09/2013 | 08/09/2013 | 1 |
8 | 09/09/2013 | 15/09/2013 | 2 |
9 | 16/09/2013 | 22/09/2013 | 3 |
10 | 23/09/2013 | 29/09/2013 |
<tbody>
</tbody>