Week Number

[ongengthai1973]

I am facing problem on week number for year 2022. Need you guys to solve.

I use formula : =WEEKNUM(I4) and Excel show 1
If I use formula : = WEEKNUM(D5) and Excel show 2

Question : Is there any formula to calculate 1/03/22 to 1/08/22 show week#1? instead of week#2.

Thank you,
ET.Ong

 

[BigBeachBananas]

Try:

=ISOWEEKNUM(DATE(2022,1,I4))

 

[Rick Rothstein]

If you want week number 1 to be 1/3 to 1/8, what week number is 1/1 in?

 

[fhqwgads]

calculate 1/03/22 to 1/08/22 show week#1?

your week has only 6 days?
or do you mean 1/02/22 to 1/08/22? or 1/03/22 to 1/09/22?

if 1/03/22 to 1/09/22 you can try

Book3
	A	B
1	1	3/01/2022
2	1	9/01/2022
3	2	10/01/2022
4	2	16/01/2022
5	3	17/01/2022
6	3	23/01/2022
7	4	24/01/2022
8	4	30/01/2022
9	5	31/01/2022
Sheet1

Cell Formulas
Range	Formula
A1:A9	A1	=ISOWEEKNUM(B1-DATE(2022,1,1))

 

[ongengthai1973]

If you want week number 1 to be 1/3 to 1/8, what week number is 1/1 in?

I just want to show 1

 

[BigBeachBananas]

I just want to show 1

To be clear, you want week 1, week 1, week 2, week 3, ....
Week 1 appears twice? Your calendar will only have 51 weeks.

 

[Rick Rothstein]

What is your criteria for determining Week #1?

 

[ongengthai1973]

To be clear, you want week 1, week 1, week 2, week 3, ....
Week 1 appears twice? Your calendar will only have 51 weeks.

YES.

 

[BigBeachBananas]

YES.

I mean you can just hardcode week 1 for the first week, then use formula after that. Maybe this:

Book1
	B	C	D	E	F	G	H	I
2		January
3	Week	Sun	Mon	Tue	Wed	Thu	Fri	Sat
4	1							1
5	1	2	3	4	5	6	7	8
6	2	9	10	11	12	13	14	15
7	3	16	17	18	19	20	21	22
8	4	23	24	25	26	27	28	29
9	5	30	31
Sheet2

Cell Formulas
Range	Formula
D5:H5	D5	=C5+1
B6:B9	B6	=ISOWEEKNUM(DATE(2022,1,C6))+1
C6:I8,C9:D9	C6	=C5+7

 

[ongengthai1973]

Pp

your week has only 6 days?
or do you mean 1/02/22 to 1/08/22? or 1/03/22 to 1/09/22?

if 1/03/22 to 1/09/22 you can try

Book3
	A	B
1	1	3/01/2022
2	1	9/01/2022
3	2	10/01/2022
4	2	16/01/2022
5	3	17/01/2022
6	3	23/01/2022
7	4	24/01/2022
8	4	30/01/2022
9	5	31/01/2022
Sheet1

Cell Formulas
Range	Formula
A1:A9	A1	=ISOWEEKNUM(B1-DATE(2022,1,1))

formula work so well…thank you