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
doublewitt Posts 14 Registration date Sunday April 27, 2014 Status Member Last seen September 29, 2014 - Sep 29, 2014 at 11:49 AM
Related:
- How to count date cells for monday of this week
- Count if cell contains number - Forum - Excel
- Count all cells containing a particular text ✓ - Forum - Excel
- Help me to count 2 cells ✓ - Forum - Excel
- Count if cell contains any text ✓ - Forum - Excel
- This formula is used to count the value in one or more cells (this will count the cells irrespective of number or text value). ✓ - Forum - Office Software
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
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
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
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
2913
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 21, 2022
541
Sep 29, 2014 at 11:35 AM
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
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
Sep 29, 2014 at 11:49 AM
very good - thank you...!
