from date data counting how many dates fall in current week

[KK1234]

Hi
I have range of different dates in A2:A100
I want to count how many dates fall under current week ( starting from Monday to Sunday)
It also needs to update itself as per current year, month etc.
I am looking for a formula.

Any help please

Kind regards

[Steve N.]

Will the dates span more than one year or all be in the current year?

[FDibbins]

Hard to offer specific answers without seeing any data, but take a look at using =weeknum() to give you the week number of the year/date

[canapone]

Hi,

I'm working on Excel 2010

An attempt


------------------------------------------------------------------------------------------------

=SUMPRODUCT(--(WEEKNUM(INDEX(A2:A100+0,),2)=WEEKNUM(TODAY(),2)))

------------------------------------------------------------------------------------------------

Hope it helps

Hope it helps

[benishiryo]

hi KK1234. for current week:
=COUNTIFS(A2:A100,">"&TODAY()-WEEKDAY(TODAY(),2),A2:A100,"<="&TODAY()-WEEKDAY(TODAY(),2)+7)

current year:
=COUNTIFS(A2:A100,">="&"1jan"&YEAR(TODAY()),A2:A100,"<="&"31dec"&YEAR(TODAY()))

current month:
=COUNTIFS(A2:A100,">"&EOMONTH(TODAY(),-1),A2:A100,"<="&EOMONTH(TODAY(),0))

[KK1234]

Hi everyone,
Really thanks for your reply.
Solution by benishiryo is a perfect one and is interesting one.
Solution by CANAPONE also works fine.
I have click * for both as a expression of my gratitude.

I have attached the workbook and there are certain question still need few answers and hope you will be able to offer help.
Thanks to Steve N. and FDibbins for your prompt reply. Please have a look at the book and see if you can help as well.
regards

[canapone]

Hi,

in Q3 and below confirmed with control+shift+enter



=IFERROR(INDEX($A$2:$A$366,SMALL(IF(WEEKNUM(INDEX($A$2:$A$366+0,),2)=WEEKNUM(TODAY(),2),ROW($A$2:$A$366)-1),ROWS($A$1:A1))),"")


in order to get dates from list if weeknum is the same.


Hope it helps

[KK1234]

Hi CANAPONE ,
Perfect solution my friend.

It all now resolved.
I am amazed, how quickly the solution is found.
I will now study all the solutions and understand them.
Great help in my learning.
I have clicked* to express my gratitude.
Thanks again.
Regards

[KK1234]

Hi CANAPONE ,
Perfect solution my friend.

It all now resolved.
I am amazed, how quickly the solution is found.
I will now study all the solutions and understand them.
Great help in my learning.
I have clicked* to express my gratitude.
Thanks again.
Regards

[daddylonglegs]

Personally I would avoid using WEEKNUM because it only gives you partial weeks at the beginning and end of the year - so Tuesday 31st Dec 2013 would be deemed to be in a different week than Wed 1st Jan 2014. If you want those to be included as the same week then I'd go with benishiryo's solution

[canapone]

Hi again,

I was about to write that I prefer Benishiro's solution too.

Anyway, thanks for your generous feedback.

[KK1234]

Hi daddylonglegs ,
You are very right. I did try and then realized what you are saying.
I will go for benishiryo's solution as well.
Thanks again.
regards

[KK1234]

I wish I could give benishiryo another *
I think you all are stars for me.
Regards