Formula to change selected week number and show the month

[Lukma]

Hi Guys

I was trying to something and i really don't no how to go about it, so if there is anyone that can help with this

i have enter a serial number using it as week number for every month so 1 to 53 Number which make it a full year, so i need formula that will enter the number of it will change to the exact month

so i need the month to change immediately i enter a value number in column B then the month change. is there any formula that could solve this

ILSP_Performance Tracking Data.xlsx
	B	C
1	Wk No.	Month
2	1	Jan-21
3	2	Jan-21
4	3	Jan-21
5	4	Jan-21
6	5	Feb-21
Sheet3

 

[JamesCanale]

So this formula will tell you the month of the saturday ending each week number.

MrExcelPlayground2.xlsx
	A	B	C
17	Week	2021	Saturday day of month
18	1	Jan 2021	2
19	2	Jan 2021	9
20	3	Jan 2021	16
21	4	Jan 2021	23
22	5	Jan 2021	30
23	6	Feb 2021	6
24	7	Feb 2021	13
25	8	Feb 2021	20
26	9	Feb 2021	27
27	10	Mar 2021	6
28	11	Mar 2021	13
29	12	Mar 2021	20
30	13	Mar 2021	27
31	14	Apr 2021	3
32	15	Apr 2021	10
33	16	Apr 2021	17
34	17	Apr 2021	24
35	18	May 2021	1
36	19	May 2021	8
37	20	May 2021	15
38	21	May 2021	22
39	22	May 2021	29
40	23	Jun 2021	5
41	24	Jun 2021	12
42	25	Jun 2021	19
43	26	Jun 2021	26
44	27	Jul 2021	3
45	28	Jul 2021	10
46	29	Jul 2021	17
47	30	Jul 2021	24
48	31	Jul 2021	31
49	32	Aug 2021	7
50	33	Aug 2021	14
51	34	Aug 2021	21
52	35	Aug 2021	28
53	36	Sep 2021	4
54	37	Sep 2021	11
55	38	Sep 2021	18
56	39	Sep 2021	25
57	40	Oct 2021	2
58	41	Oct 2021	9
59	42	Oct 2021	16
60	43	Oct 2021	23
61	44	Oct 2021	30
62	45	Nov 2021	6
63	46	Nov 2021	13
64	47	Nov 2021	20
65	48	Nov 2021	27
66	49	Dec 2021	4
67	50	Dec 2021	11
68	51	Dec 2021	18
69	52	Dec 2021	25
70	53	Jan 2022	1
Sheet19

Cell Formulas
Range	Formula
B18:B70	B18	=DATE($B$17,1,1)+7*(A18-1)+(7-WEEKDAY(DATE($B$17,1,1),1))
C18:C70	C18	=DAY(B18)
A19:A70	A19	=A18+1

 

[Eric W]

How do you determine the first week? Is it always Jan 1 - Jan 7? Or do you start on the first Monday of the year or something? How do you want to handle weeks that span 2 months? First day of week or last?

Assuming week 1 is Jan 1-Jan 7, and you want the month of the first day of the week, try this:

Book1
	A	B
1	Wk No.	Month
2	1	Jan-21
3	2	Jan-21
4	3	Jan-21
5	4	Jan-21
6	5	Jan-21
7	6	Feb-21
8	7	Feb-21
9	8	Feb-21
10	9	Feb-21
11	10	Mar-21
12	11	Mar-21
Sheet7

Cell Formulas
Range	Formula
B2:B12	B2	=DATE(YEAR(TODAY()),1,A2*7-6)

 

[Lukma]

Dear Eris W

Thanks with the formula easy and simple,

Actual i was trying to find the average for each week by doing the so once i click the dropdown week number in column S1 then in column T2 will change to get the average but i guess your formula helps out
But if you feel it ok just the way i use it to find the average please do let me know or you could just assist if there is more better ways

ILSP_Performance Tracking Data.xlsx
	N	O	P	Q	R	S	T	U
1	Month	Wk- No	Date & Time	Date & Time	Hrs	1	Month	YES
2	01-Jan-21	53	31/12/20 21:34	01/01/21 00:50	3:16	YES	Jan-21
3	01-Jan-21	53	31/12/20 19:09	31/12/20 23:00	3:51	NO	Average Wekly
4	01-Jan-21	53	31/12/20 22:08	01/01/21 11:30	13:22	NO	9:25
5	01-Jan-21	1	01/01/21 04:14	01/01/21 01:15	-02:59	NO
6	01-Jan-21	53	31/12/20 23:00	01/01/21 08:30	9:30	YES
7	01-Jan-21	1	01/01/21 11:30	01/01/21 05:20	-06:10	YES
8	01-Jan-21	1	01/01/21 05:05	01/01/21 15:09	10:04	YES
9	01-Jan-21	1	01/01/21 04:45	01/01/21 09:45	5:00	YES
10	01-Jan-21	1	01/01/21 09:58	01/01/21 08:05	-01:53	YES
11	01-Jan-21	1	02/01/21 12:39	01/01/21 13:50	-22:49	YES
12	01-Jan-21	1	02/01/21 20:35	02/01/21 16:00	-04:35	YES
13	01-Jan-21	1	01/01/21 19:05	03/01/21 00:15	29:10	YES
14	01-Jan-21	1	01/01/21 17:06	02/01/21 00:00	6:54	NO
15	01-Jan-21	1	02/01/21 04:44	01/01/21 21:40	-07:04	NO
16	01-Jan-21	1	01/01/21 21:00	02/01/21 08:30	11:30	YES
17	01-Jan-21	1	02/01/21 05:52	02/01/21 01:00	-04:52	NO
18	01-Jan-21	1	01/01/21 19:14	02/01/21 10:25	15:11	NO
19	01-Jan-21	1	01/01/21 19:30	02/01/21 00:15	4:45	NO
20	01-Jan-21	1	01/01/21 22:40	01/01/21 23:55	1:15	NO
21	01-Jan-21	1	01/01/21 16:30	02/01/21 03:50	11:20	YES
22	01-Jan-21	1	02/01/21 09:35	01/01/21 17:30	-16:05	NO
23	01-Jan-21	1	02/01/21 08:48	02/01/21 13:05	4:17	NO
24	01-Jan-21	2	03/01/21 00:45	02/01/21 12:18	-12:27	NO
25	01-Jan-21	1	02/01/21 18:30	03/01/21 04:00	9:30	YES
26	01-Jan-21	2	03/01/21 00:46	02/01/21 22:20	-02:26	NO
27	01-Jan-21	1	02/01/21 22:10	03/01/21 04:00	5:50	NO
28	01-Jan-21	1	02/01/21 18:20	03/01/21 02:00	7:40	YES
29	01-Jan-21	2	03/01/21 08:50	02/01/21 22:30	-10:20	YES
30	01-Jan-21	2	03/01/21 22:30	03/01/21 12:05	-10:25	NO
31	01-Jan-21	2	03/01/21 11:20	04/01/21 02:15	14:55	YES
32	01-Jan-21	2	03/01/21 16:18	03/01/21 18:25	2:07	YES
33	01-Jan-21	2	03/01/21 12:46	03/01/21 23:30	10:44	YES
34	01-Jan-21	2	03/01/21 18:15	03/01/21 16:25	-01:50	NO
35	01-Jan-21	2	03/01/21 21:30	04/01/21 02:10	4:40	YES
36	01-Jan-21	2	03/01/21 21:12	04/01/21 00:15	3:03	YES
37	01-Jan-21	2	03/01/21 16:20	04/01/21 01:40	9:20	YES
38	01-Jan-21	2	03/01/21 17:02	04/01/21 00:05	7:03	YES
39	01-Jan-21	2	03/01/21 22:14	03/01/21 23:42	1:28	NO
40	01-Jan-21	2	04/01/21 03:40	04/01/21 02:12	-01:28	YES
41	01-Jan-21	2	04/01/21 21:30	04/01/21 16:50	-04:40	NO
Sheet3

Cell Formulas
Range	Formula
T2	T2	=DATE(YEAR(TODAY()),1,S1*7-6)
T4	T4	=IFERROR(AVERAGEIFS(R2:R41,S2:S41,"<>0"&U1,O2:O41,S1,N2:N41,T2),"")
O2:O41	O2	=IF(P2<>"",WEEKNUM(P2,1),"")
R2:R41	R2	=IFERROR(IF(Q2-P2<0, "-" & TEXT(ABS(Q2-P2),"hh:mm"), Q2-P2),"")

Cells with Data Validation
Cell	Allow	Criteria
S1	List	=$H$3:$H$44

 

[Eric W]

As far as I can tell, your way to find the average should work. The T4 formula is possibly a bit redundant, since it checks columns N and O, and the values there are 1-1 matches, but it shouldn't hurt anything.

Let us know if you have any more questions!

 

[Lukma]

Hi Eric

Thanks so much, but am having a trouble with Column S1 and T2 whenever i click the drop down in column S1 to change the week number and to give the average of the following week this wont give any result or retain the month,
My main aim is that once i change the Week is Column S1 to give the the result for wk-1 average and then when i change to Wk2 it should be me result for Wk2 as well for same month till jan week is completed then when i change to the Wk-6 it should auto change the Month in column T2 and give me week 6 average.

I hope my explanation should be clear

Appreciate your feedback

 

[Lukma]

HI Eric

Thanks so much i feel it change the month but still am having problem with the average, whenever i change the drop down to the following week the result on T4 wont calculate or give the average.

Please can you review with this if i have made a mistake some how

Regards

 

[Lukma]

thanks Eric
it work i was missing out some few entry so i update all

Really grateful thanks so much