How to count date cells for monday of this week
Solved/Closed
doublewitt
Posts
14
Registration date
Sunday 27 April 2014
Status
Member
Last seen
29 September 2014
-
25 Sep 2014 à 07:53
doublewitt Posts 14 Registration date Sunday 27 April 2014 Status Member Last seen 29 September 2014 - 29 Sep 2014 à 11:49
doublewitt Posts 14 Registration date Sunday 27 April 2014 Status Member Last seen 29 September 2014 - 29 Sep 2014 à 11:49
Related:
- Whatsapp celebrates its 11th birthday colorful keyboard whatsapp's birthday get 120 new emojis an official whatsapp message hurry the deadline your keyboard message to 25 people attention how cute send this message
- Count number of occurrences in excel - Guide
- Count names in excel - Guide
- How to add week number in windows calendar - Guide
- Google calendar week numbers - Guide
- Based on the values in cells b77 b88 - Excel Forum
3 responses
TrowaD
Posts
2921
Registration date
Sunday 12 September 2010
Status
Contributor
Last seen
27 December 2022
555
25 Sep 2014 à 11:15
25 Sep 2014 à 11:15
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
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 27 April 2014
Status
Member
Last seen
29 September 2014
25 Sep 2014 à 12:01
25 Sep 2014 à 12:01
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 12 September 2010
Status
Contributor
Last seen
27 December 2022
555
29 Sep 2014 à 11:35
29 Sep 2014 à 11:35
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
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 27 April 2014
Status
Member
Last seen
29 September 2014
29 Sep 2014 à 11:49
29 Sep 2014 à 11:49
very good - thank you...!
