# Sum the number of dates that fall on a specific week

Dear experts,

1. There is a list of dates in A1 to A10
2. In A11 I need a formula which counts how many of those dates fall in week 5.

Br,
Juan
Microsoft ExcelMicrosoft Office

Last Comment
Ejgil Hedegaard
Subodh Tiwari (Neeraj)

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Ryan Chong

for cell B1 you can use this formula instead:

``````=WEEKNUM(A1,2)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),2)+1
``````
The weeknumber function in Excel start week 1 on the first day of the year.
This means that for 2017, week 1 is only 1 day.

If you want to use ISO calender weeks, use this in A11.
``````=SUMPRODUCT(((INT((A1:A10-DATE(YEAR(A1:A10-WEEKDAY(A1:A10-1)+4),1,3)+WEEKDAY(DATE(YEAR(A1:A10-WEEKDAY(A1:A10-1)+4),1,3))+5)/7))=5)*1)
``````
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY