Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

How to count date cells for monday of this week [Solved/Closed]

Report
Posts
14
Registration date
Sunday April 27, 2014
Status
Member
Last seen
September 29, 2014
 -
Posts
14
Registration date
Sunday April 27, 2014
Status
Member
Last seen
September 29, 2014
 -
Hello,

I would like to count the number of cells with date = to MONDAY of this current week and also MONDAY of the following week within a defined range. Would appreciate your guidance...

Thanks

See more

3 replies

Answer 1 / 3
Posts
2657
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 22, 2020
 440
Hi Doublewitt,

I entered date's in the range A1:A30 starting with 15-9-2014 and ending with 14-10-2014.

The following formula gives me 8 as a result (Monday of current week):
=COUNTIF(A1:A30,"<=22-9-2014")

This formula gives me 15 as a result (Monday of next week):
=COUNTIF(A1:A30,"<=29-9-2014")

Best regards,
Trowa
Answer 2 / 3
Posts
14
Registration date
Sunday April 27, 2014
Status
Member
Last seen
September 29, 2014
thanks for the effort but that's not quite what I need - perhaps I need to explain better. Can we create a formula based on the year's current week number in another cell? In other words, count total number of cells containing monday's date within current week 39.
Report
Posts
2657
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 22, 2020
 440
Hi Doublewitt,

So you want to count the number of times 22-9-2014 occurs in a range?:
=COUNTIF(A1:A30,"=22-9-2014")

or you can use an additional column to check if the date matches your criteria with this formula:
=IF(AND(WEEKNUM(A1)=39,WEEKDAY(A1)=2),1,0)
If the date is a Monday in week 39 then the cell shows a 1, you can then add all the 1's to get a total.

Is this what you were looking for?

Best regards,
Trowa
Answer 3 / 3
Posts
14
Registration date
Sunday April 27, 2014
Status
Member
Last seen
September 29, 2014
very good - thank you...!
Ask a question