Weeks Total calc from drop-down list of Months.

oftwominds

New Member
Joined
Feb 14, 2022
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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.


Screenshot 2024-04-05 121046.png
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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
ABCDEFGHIJKLMNO
1Mar2024Jan
2Feb
3Mar
4Apr
5May
6Jun
7Jul
8Aug
9Sep
10Insurable WeeksOct
11Nov
12Dec
Sheet1
Cells with Data Validation
CellAllowCriteria
F1List=$N$1:$N$12
 
Upvote 0
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
ABCDEFGHIJKLMN
1Apr2024Jan
2Feb
3Mar
4Apr
5May
6Jun
7Jul
8weeknumberdays/7Aug
9Sep
101817.1428571Oct
11Nov
12Dec
13Jan
14Feb
15Mar
16Apr
17May
18Jun
Sheet4
Cell Formulas
RangeFormula
L10L10=WEEKNUM(F1-N1)
M10M10=DAYS(F1,N1)/7
 
Upvote 0
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
ABCDEFGHIJKLMN
1Apr2024Jan
2Feb
3Mar
4Apr
5May
6Jun
7Jul
8weeknumberdays/7Aug
9Sep
101817.1428571Oct
11Nov
12Dec
13Jan
14Feb
15Mar
16Apr
17May
18Jun
Sheet4
Cell Formulas
RangeFormula
L10L10=WEEKNUM(F1-N1)
M10M10=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.
 

Attachments

  • Screenshot 2024-04-05 131808.png
    Screenshot 2024-04-05 131808.png
    11.6 KB · Views: 6
Upvote 0
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

Cell Formulas
RangeFormula
C3:C18C3=WEEKNUM(B3)
D4:D18D4=WEEKNUM(B4-$B$3)
E4:E18E4=DAYS(B4,$B$3)/7
A3:A18A3=B3
B7:B18B7=EOMONTH(O1,0)
M10M10=WEEKNUM(M1-O1)
N10N10=DAYS(M1,O1)/7
N12N12=INT(N10)
 
Upvote 0
Solution
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

Cell Formulas
RangeFormula
C3:C18C3=WEEKNUM(B3)
D4:D18D4=WEEKNUM(B4-$B$3)
E4:E18E4=DAYS(B4,$B$3)/7
A3:A18A3=B3
B7:B18B7=EOMONTH(O1,0)
M10M10=WEEKNUM(M1-O1)
N10N10=DAYS(M1,O1)/7
N12N12=INT(N10)
Thanks etaf
 
Upvote 0
you are welcome
did any of those suggestions work???
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top