Current week formual

Hi all,

I am trying to count the number of planned activities for the current week using Planned Start Date column. I have the following formula, but it does not work. Does any one have any idea.

=COUNTIFS([Planned Start Date]:[Planned Start Date], >= TODAY() - WEEKDAY(TODAY(), 2) + 1, [Planned Start Date]:[Planned Start Date], <= TODAY() - WEEKDAY(TODAY(), 2) + 7)

Answers

  • Melissa Yamada
    Melissa Yamada ✭✭✭✭✭

    Hello @SASGE

    Try this:

    =COUNTIFS([Planned Start Date]:[Planned Start Date], WEEKNUMBER(TODAY()) = IFERROR(WEEKNUMBER(@cell ), 0))

    Melissa Yamada
    melissa@insightfulsheets.com
    Data made simple, spreadsheets reimagined

  • Hello Melissa,

    Thank you for the help. The formula seems to be working with one error, it returns on extra activity. I have 2 activities planned for the week, but it returns 3. I have tested with different columns and different scenarios, but it always returns one more (if I have 5 activates, it returns 6).

    I am not sure what is going on.

    Regards

    Sayed

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!