Menu
Ask a question Report

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

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

Report Respond to TrowaD
Posts
2447
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 15, 2018
 - 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
Report Respond to doublewitt
Posts
14
Registration date
Sunday April 27, 2014
Last seen
September 29, 2014
 - 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.
Report
Posts
2447
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 15, 2018
 - 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
Report Respond to doublewitt
Posts
14
Registration date
Sunday April 27, 2014
Last seen
September 29, 2014
 - Sep 29, 2014 at 11:49 AM
0
Thank you
very good - thank you...!
Ask a question