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

3 replies

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
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
0
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.
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
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
0
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...!
0