Weeks Total calc from drop-down list of Months.

[oftwominds]

Hi, how can I calculate the total number of weeks in M10 to the end of a specified month from a drop-down list and a specified year. The answer for Mar 2024 should be 13 but unable to resolve it. See the screenshot example below. cells F1 and G1 are formatted for text. Thanks.

 

[oftwominds]

Hi, how can I calculate the total number of weeks in M10 to the end of a specified month from a drop-down list and a specified year. The answer for Mar 2024 should be 13 but unable to resolve it. See the screenshot example below. cells F1 and G1 are formatted for text. Thanks.


View attachment 109524

Weeks calc.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1						Mar	2024							Jan
2														Feb
3														Mar
4														Apr
5														May
6														Jun
7														Jul
8														Aug
9														Sep
10											Insurable Weeks			Oct
11														Nov
12														Dec
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
F1	List	=$N$1:$N$12

 

[etaf]

how you define a week
do you want to count the days and divid by 7
April for example finished on a tuesday
so i guess would be 18 weeks or

=DAYS(F1,N1)/7
OR
=WEEKNUM(F1-N1)

if you can have in the dropdown the end of all the months
and dates in column N formatted as MMM

then the year will also be taken into account

IF just text than a little more complicated
BUT 1st how do you want to calculate the weeks exactly

i have used april as it stops in the middle of a week

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1						Apr	2024							Jan
2														Feb
3														Mar
4														Apr
5														May
6														Jun
7														Jul
8												weeknumber	days/7	Aug
9														Sep
10												18	17.1428571	Oct
11														Nov
12														Dec
13														Jan
14														Feb
15														Mar
16														Apr
17														May
18														Jun
Sheet4

Cell Formulas
Range	Formula
L10	L10	=WEEKNUM(F1-N1)
M10	M10	=DAYS(F1,N1)/7

 

[oftwominds]

how you define a week
do you want to count the days and divid by 7
April for example finished on a tuesday
so i guess would be 18 weeks or

=DAYS(F1,N1)/7
OR
=WEEKNUM(F1-N1)

if you can have in the dropdown the end of all the months
and dates in column N formatted as MMM

then the year will also be taken into account

IF just text than a little more complicated
BUT 1st how do you want to calculate the weeks exactly

i have used april as it stops in the middle of a week

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1						Apr	2024							Jan
2														Feb
3														Mar
4														Apr
5														May
6														Jun
7														Jul
8												weeknumber	days/7	Aug
9														Sep
10												18	17.1428571	Oct
11														Nov
12														Dec
13														Jan
14														Feb
15														Mar
16														Apr
17														May
18														Jun
Sheet4

Cell Formulas
Range	Formula
L10	L10	=WEEKNUM(F1-N1)
M10	M10	=DAYS(F1,N1)/7

Hi, thanks for reply. I guess looking to replicate weeks as listed in Outlook calendar so based on 7 day week. Need just similar whole number in cell output.

 

[etaf]

i dont know how excel can lookup outlook calendar

so if based on a 7day week in my example , then you can use INT()
so the weeknum()
you can make the weeknum() start on different days

it looks like weeknum() may work for you

BUT that needs dates in the cells formatted as MMM

here are some dates and weeks

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1													Apr	2024	Jan
2			weeknumber	weeknum calc	days										Feb
3	Mon	1/1/24	1												Mar
4	Tue	4/30/24	18	18	17.14										Apr
5	Sun	3/31/24	14	13	12.86										May
6	Thu	10/31/24	44	44	43.43										Jun
7	Wed	1/31/24	5	5	4.29										Jul
8	Thu	2/29/24	9	9	8.43								weeknumber	days/7	Aug
9	Sun	3/31/24	14	13	12.86										Sep
10	Tue	4/30/24	18	18	17.14								18	17.1428571	Oct
11	Fri	5/31/24	22	22	21.57										Nov
12	Sun	6/30/24	27	26	25.86									17	Dec
13	Wed	7/31/24	31	31	30.29										Jan
14	Sat	8/31/24	35	35	34.71										Feb
15	Mon	9/30/24	40	39	39.00										Mar
16	Thu	10/31/24	44	44	43.43										Apr
17	Sat	11/30/24	48	48	47.71										May
18	Tue	12/31/24	53	53	52.14										Jun
Sheet4

Cell Formulas
Range	Formula
C3:C18	C3	=WEEKNUM(B3)
D4:D18	D4	=WEEKNUM(B4-$B$3)
E4:E18	E4	=DAYS(B4,$B$3)/7
A3:A18	A3	=B3
B7:B18	B7	=EOMONTH(O1,0)
M10	M10	=WEEKNUM(M1-O1)
N10	N10	=DAYS(M1,O1)/7
N12	N12	=INT(N10)

 

[oftwominds]

i dont know how excel can lookup outlook calendar

so if based on a 7day week in my example , then you can use INT()
so the weeknum()
you can make the weeknum() start on different days

it looks like weeknum() may work for you

BUT that needs dates in the cells formatted as MMM

here are some dates and weeks

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1													Apr	2024	Jan
2			weeknumber	weeknum calc	days										Feb
3	Mon	1/1/24	1												Mar
4	Tue	4/30/24	18	18	17.14										Apr
5	Sun	3/31/24	14	13	12.86										May
6	Thu	10/31/24	44	44	43.43										Jun
7	Wed	1/31/24	5	5	4.29										Jul
8	Thu	2/29/24	9	9	8.43								weeknumber	days/7	Aug
9	Sun	3/31/24	14	13	12.86										Sep
10	Tue	4/30/24	18	18	17.14								18	17.1428571	Oct
11	Fri	5/31/24	22	22	21.57										Nov
12	Sun	6/30/24	27	26	25.86									17	Dec
13	Wed	7/31/24	31	31	30.29										Jan
14	Sat	8/31/24	35	35	34.71										Feb
15	Mon	9/30/24	40	39	39.00										Mar
16	Thu	10/31/24	44	44	43.43										Apr
17	Sat	11/30/24	48	48	47.71										May
18	Tue	12/31/24	53	53	52.14										Jun
Sheet4

Cell Formulas
Range	Formula
C3:C18	C3	=WEEKNUM(B3)
D4:D18	D4	=WEEKNUM(B4-$B$3)
E4:E18	E4	=DAYS(B4,$B$3)/7
A3:A18	A3	=B3
B7:B18	B7	=EOMONTH(O1,0)
M10	M10	=WEEKNUM(M1-O1)
N10	N10	=DAYS(M1,O1)/7
N12	N12	=INT(N10)

Thanks etaf

 

[etaf]

you are welcome
did any of those suggestions work???