Stuck on completed in the last 7 days formula / function

Molly12345

Hi David, thank you so much for your reply . That was not long winded at all, but extremely helpful and informative. I am learning smartsheet through the comments here.

When I put this formula in =COUNTIF({planned executions}, @cell >TODAY()) + COUNTIF({planned executions}, @cell <TODAY(8))

For how many due this week I get 61, even though I have one due today and one due on the 24th. I have created this formula so many time the last week and either get 0 or 61.

Thank you again for taking the time to help me. I cannot explain how much I appreciate it !

David Tutwiler

I think you were so very close on this. I think the only hiccup now is your "in the past" part of the formula. You have inverted the sign from our previous function, which I get because you're trying to invert it, but I think it accidentally set up the wrong range for your function.

TODAY(8) is the same as TODAY() + 8 days. So you are looking for anything less than a week and a day into the future. I think what you're actually looking for is anything that is older than a week in the past from today. Which should look like < TODAY(-7) [which would be TODAY() - 7]. So the whole function would look like:

=COUNTIF({planned executions}, @cell >TODAY()) + COUNTIF({planned executions}, @cell < TODAY(-7))

Sorry for not catching that in the previous post.

Molly12345

Hi David,

thanks so much for the explanations here, I really appreciate it. I followed =COUNTIF({planned executions}, @cell >TODAY()) + COUNTIF({planned executions}, @cell < TODAY(-7)), for what is due in the next 7 days and I am still getting 61 for some strange reason. 🤔

Molly12345

Thank you David for help on more than one occasion !

David Tutwiler

The range we have worked out, I thought, was for the inverse of what is due this week. That's why the function looks for what was due in the past (longer than 7 days ago) and what is due in the future (greater than today). For that logic, the function should be working correctly and I would go and check your date ranges to make sure there isn't something that you think should fall out of that range that might be formatted incorrectly.

If it gets really dire you could temporarily share the sheet with me, but I don't want you to have to do that unless you absolutely have to.

Molly12345

Hi David sorry now I am completely confused. Apologies to cause confusion also.

so the formula for what was completed in the last 7 days works perfectly, kudos to you, but I am now struggling to get what is due in the next 7 days. Including today. Apologies if you understand this already! I have checked the dates are formatted correctly

Molly12345

Also thank you so much for the help !

David Tutwiler

I think I need to apologize because I think I misunderstood the assignment here. Reading back over your comments you made it clear that was what you were looking for and I provided a formula that did something different.

If we go back to your original formula you posted as the inverse and just add the @cell into it, I think you have what you need. Sorry for the confusion.

=COUNTIFS({planned executions}, @cell >TODAY(),{planned executions}, @cell < TODAY(8))

Molly12345

Wahoo! It is me who needs to apologize for an incorrect explanation and subsequent wasting of your time! I cannot thank you enough for your help here. You are due some very good fortune!

Have a great week and thank you for all your teachings 👏

David Tutwiler

No problem, glad it's all working!