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

doublewitt 14 Posts Sunday April 27, 2014Registration date September 29, 2014 Last seen - Sep 25, 2014 at 07:53 AM - Latest reply: doublewitt 14 Posts Sunday April 27, 2014Registration date September 29, 2014 Last seen
- Sep 29, 2014 at 11:49 AM
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 

4 replies

TrowaD 2396 Posts Sunday September 12, 2010Registration dateModeratorStatus July 19, 2018 Last seen - Sep 25, 2014 at 11:15 AM
0
Thank you
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
doublewitt 14 Posts Sunday April 27, 2014Registration date September 29, 2014 Last seen - Sep 25, 2014 at 12:01 PM
0
Thank you
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.
TrowaD 2396 Posts Sunday September 12, 2010Registration dateModeratorStatus July 19, 2018 Last seen - Sep 29, 2014 at 11:35 AM
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
doublewitt 14 Posts Sunday April 27, 2014Registration date September 29, 2014 Last seen - Sep 29, 2014 at 11:49 AM
0
Thank you
very good - thank you...!