How to count date cells for monday of this week

Solved/Closed
doublewitt Posts 14 Registration date Sunday April 27, 2014 Status Member Last seen September 29, 2014 - Sep 25, 2014 at 07:53 AM
doublewitt Posts 14 Registration date Sunday April 27, 2014 Status Member Last seen September 29, 2014 - 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

Related:

3 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Sep 25, 2014 at 11:15 AM
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 Posts 14 Registration date Sunday April 27, 2014 Status Member Last seen September 29, 2014
Sep 25, 2014 at 12:01 PM
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 Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
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 Posts 14 Registration date Sunday April 27, 2014 Status Member Last seen September 29, 2014
Sep 29, 2014 at 11:49 AM
very good - thank you...!