Forum Discussion
How can I Calculate to amount on Mondays in my header but only the mondays in my headers?
I have headers B1:GY1 that are dated but it is not constant days in a row, it is a school schedule & I need to count the amount of Mondays, Tuesdays, etc. up to today's date when today's date is located in the headers. (The days missing are usually holidays, Snow Days or school breaks)
I have figured to count them between two dates but I cannot figure out the formula to count this. How should I formulate to solve this problem?
For Mondays:
=SUMPRODUCT((B1:GY1<=TODAY())*(WEEKDAY(B1:GY1)=2))
The number 2 near the end of the formula is the day of the week:
1=Sunday
2=Monday
3=Tuesday
...
7=Saturday
5 Replies
For Mondays:
=SUMPRODUCT((B1:GY1<=TODAY())*(WEEKDAY(B1:GY1)=2))
The number 2 near the end of the formula is the day of the week:
1=Sunday
2=Monday
3=Tuesday
...
7=Saturday
HansVogelaar When I input the formula I get a #Value error back, this is how it is set up below.
b1 c1 d1 e1 f1 gu1 gv1 gw1 gz1 gy1 Wednesday, August 18, 2021 Thursday, August 19, 2021 Friday, August 20, 2021 Monday, August 23, 2021 Tuesday, August 24, 2021 Tuesday, July 19, 2022 Wednesday, July 20, 2022 Thursday, July 21, 2022 Friday, July 22, 2022 Monday, July 25, 2022 Make sure that the values in B1 to GY1 are dates formatted as dddd, mmmm d, yyyy and not text values that look like dates.
If you don't have Microsoft 365 or Office 2021, you may have to confirm the formula with Ctrl+Shift+Enter.
- Thank you so much, I will put it to work tomorrow & let you know how it works.
