How to count date cells for monday of this week
Solved/Closed
doublewitt
doublewitt
- Posts
- 14
- Registration date
- Sunday April 27, 2014
- Status
- Member
- Last seen
- September 29, 2014
doublewitt
- Posts
- 14
- Registration date
- Sunday April 27, 2014
- Status
- Member
- Last seen
- September 29, 2014
Related:
- How to count date cells for monday of this week
- Count if cell contains any text ✓ - Forum - Excel
- Count if cell contains number - Forum - Excel
- Count all cells containing a particular text ✓ - Forum - Excel
- Help me to count 2 cells ✓ - Forum - Excel
- Counta is counting blank cells ✓ - Forum - Excel
3 replies
TrowaD
- Posts
- 2849
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- January 27, 2022
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
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
- 2849
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- January 27, 2022
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
very good - thank you...!
