Find week number within custom year when user enters random date

[nickpape]

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


[TABLE="width: 500"]
<tbody>[TR]
[TH]1[/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[/TR]
[TR]
[TD]2[/TD]
[TD]USER ENTRY DATE[/TD]
[TD]WK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]12/09/2013[/TD]
[TD] <formula result="" e.g="" wk="" no="2"></formula>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]WK START[/TD]
[TD]WK END[/TD]
[TD]WK NO[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]02/09/2013[/TD]
[TD]08/09/2013[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]09/09/2013[/TD]
[TD]15/09/2013[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]16/09/2013[/TD]
[TD]22/09/2013[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]23/09/2013[/TD]
[TD]29/09/2013[/TD]
[/TR]
</tbody>[/TABLE]

 

[Oeldere]

Book1
	F	G
18	12-9-2013	37
Blad1

Cell Formulas
Range	Formula
G18	=WEEKNUM(F18)

 

[Oeldere]

Book1
	F	G	H
17	1-9-2013	36
18	9-9-2013	1	ma
19	16-9-2013	2	ma
20	23-9-2013	3	ma
21	30-9-2013	4	ma
22	7-10-2013	5	ma
23	14-10-2013	6	ma
24	21-10-2013	7	ma
25	28-10-2013	8	ma
26	4-11-2013	9	ma
27	11-11-2013	10	ma
28	18-11-2013	11	ma
29	25-11-2013	12	ma
30	2-12-2013	13	ma
31	9-12-2013	14	ma
32	16-12-2013	15	ma
33	23-12-2013	16	ma
34	30-12-2013	17	ma
35	6-1-2014	18	ma
36	13-1-2014	19	ma
37	20-1-2014	20	ma
38	27-1-2014	21	ma
39	3-2-2014	22	ma
40	10-2-2014	23	ma
Blad1

Cell Formulas
Range	Formula
G17	=WEEKNUM(F17)
G18	=IF((WEEKNUM(F18)-$G$17)<0,52+WEEKNUM(F18)-$G$17,WEEKNUM(F18)-$G$17)
G19	=IF((WEEKNUM(F19)-$G$17)<0,52+WEEKNUM(F19)-$G$17,WEEKNUM(F19)-$G$17)
G20	=IF((WEEKNUM(F20)-$G$17)<0,52+WEEKNUM(F20)-$G$17,WEEKNUM(F20)-$G$17)
G21	=IF((WEEKNUM(F21)-$G$17)<0,52+WEEKNUM(F21)-$G$17,WEEKNUM(F21)-$G$17)
G22	=IF((WEEKNUM(F22)-$G$17)<0,52+WEEKNUM(F22)-$G$17,WEEKNUM(F22)-$G$17)
G23	=IF((WEEKNUM(F23)-$G$17)<0,52+WEEKNUM(F23)-$G$17,WEEKNUM(F23)-$G$17)
G24	=IF((WEEKNUM(F24)-$G$17)<0,52+WEEKNUM(F24)-$G$17,WEEKNUM(F24)-$G$17)
G25	=IF((WEEKNUM(F25)-$G$17)<0,52+WEEKNUM(F25)-$G$17,WEEKNUM(F25)-$G$17)
G26	=IF((WEEKNUM(F26)-$G$17)<0,52+WEEKNUM(F26)-$G$17,WEEKNUM(F26)-$G$17)
G27	=IF((WEEKNUM(F27)-$G$17)<0,52+WEEKNUM(F27)-$G$17,WEEKNUM(F27)-$G$17)
G28	=IF((WEEKNUM(F28)-$G$17)<0,52+WEEKNUM(F28)-$G$17,WEEKNUM(F28)-$G$17)
G29	=IF((WEEKNUM(F29)-$G$17)<0,52+WEEKNUM(F29)-$G$17,WEEKNUM(F29)-$G$17)
G30	=IF((WEEKNUM(F30)-$G$17)<0,52+WEEKNUM(F30)-$G$17,WEEKNUM(F30)-$G$17)
G31	=IF((WEEKNUM(F31)-$G$17)<0,52+WEEKNUM(F31)-$G$17,WEEKNUM(F31)-$G$17)
G32	=IF((WEEKNUM(F32)-$G$17)<0,52+WEEKNUM(F32)-$G$17,WEEKNUM(F32)-$G$17)
G33	=IF((WEEKNUM(F33)-$G$17)<0,52+WEEKNUM(F33)-$G$17,WEEKNUM(F33)-$G$17)
G34	=IF((WEEKNUM(F34)-$G$17)<0,52+WEEKNUM(F34)-$G$17,WEEKNUM(F34)-$G$17)
G35	=IF((WEEKNUM(F35)-$G$17)<0,52+WEEKNUM(F35)-$G$17,WEEKNUM(F35)-$G$17)
G36	=IF((WEEKNUM(F36)-$G$17)<0,52+WEEKNUM(F36)-$G$17,WEEKNUM(F36)-$G$17)
G37	=IF((WEEKNUM(F37)-$G$17)<0,52+WEEKNUM(F37)-$G$17,WEEKNUM(F37)-$G$17)
G38	=IF((WEEKNUM(F38)-$G$17)<0,52+WEEKNUM(F38)-$G$17,WEEKNUM(F38)-$G$17)
G39	=IF((WEEKNUM(F39)-$G$17)<0,52+WEEKNUM(F39)-$G$17,WEEKNUM(F39)-$G$17)
G40	=IF((WEEKNUM(F40)-$G$17)<0,52+WEEKNUM(F40)-$G$17,WEEKNUM(F40)-$G$17)
H18	=TEXT(WEEKDAY(F18),"ddd")
H19	=TEXT(WEEKDAY(F19),"ddd")
H20	=TEXT(WEEKDAY(F20),"ddd")
H21	=TEXT(WEEKDAY(F21),"ddd")
H22	=TEXT(WEEKDAY(F22),"ddd")
H23	=TEXT(WEEKDAY(F23),"ddd")
H24	=TEXT(WEEKDAY(F24),"ddd")
H25	=TEXT(WEEKDAY(F25),"ddd")
H26	=TEXT(WEEKDAY(F26),"ddd")
H27	=TEXT(WEEKDAY(F27),"ddd")
H28	=TEXT(WEEKDAY(F28),"ddd")
H29	=TEXT(WEEKDAY(F29),"ddd")
H30	=TEXT(WEEKDAY(F30),"ddd")
H31	=TEXT(WEEKDAY(F31),"ddd")
H32	=TEXT(WEEKDAY(F32),"ddd")
H33	=TEXT(WEEKDAY(F33),"ddd")
H34	=TEXT(WEEKDAY(F34),"ddd")
H35	=TEXT(WEEKDAY(F35),"ddd")
H36	=TEXT(WEEKDAY(F36),"ddd")
H37	=TEXT(WEEKDAY(F37),"ddd")
H38	=TEXT(WEEKDAY(F38),"ddd")
H39	=TEXT(WEEKDAY(F39),"ddd")
H40	=TEXT(WEEKDAY(F40),"ddd")

 

[nickpape]

Thanks. That is perfect. Had a mishap as I used the WEEKNUM as an absolute reference, not dynamic as per the example above. You have made me think much more. Thank you for your assistance. Greatly appreciated.

My dashboards about about to get that much better!

 

[Oeldere]

Glad I could help.

Thanks for the reply