Forum Discussion

DanW1648's avatar
DanW1648
Copper Contributor
Mar 09, 2022
Solved

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 loca...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Mar 09, 2022

    DanW1648 

    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

DanW1648's avatar
DanW1648
Copper Contributor
Mar 10, 2022

HansVogelaar When I input the formula I get a #Value error back, this is how it is set up below.

b1c1d1e1f1gu1gv1gw1gz1gy1
Wednesday, August 18, 2021Thursday, August 19, 2021Friday, August 20, 2021Monday, August 23, 2021Tuesday, August 24, 2021Tuesday, July 19, 2022Wednesday, July 20, 2022Thursday, July 21, 2022Friday, July 22, 2022Monday, July 25, 2022
HansVogelaar's avatar
HansVogelaar
MVP
Mar 10, 2022

DanW1648 

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.

  • DanW1648's avatar
    DanW1648
    Copper Contributor
    Mar 10, 2022
    This worked great, Thank you so much!