Is there a formula to have a cell checked when the due date is within 30 days of today?
I am trying to create a report that shows two items 1) Parent rows, 2) tasks under those that are due in 30 days from today. I can get the report to give me the tasks due in 30 days, but I am not getting the parents since the due dates are more than 30 days.
I was thinking I can create a helper column that automatically is checked when the due date is in 30 days and unchecked when not due within 30 days.
Best Answer
-
If you want to have the parent row displayed in the report, @Sean Morgan won't be enough I believe. It's perfectly fine for children rows, but Parents will remain out of it if there due date is in more than 30 days.
In the parent row you're gonna need to have this formula:
=IF(COUNTIF(CHILDREN(),1)>0,1,0)
This will check the box if any of the children is checked.
Now, if you want to automate the formula to be efficient on both praent and children rows, Use an helper column to determine the rank of the row:
Rank Column: =COUNT(ANCESTORS())+1
Then in your checkbox column:
=IF(Rank@row=1, IF(COUNTIF(CHILDREN(),1)>0,1,0),IF(AND(OR(Status@row = "Not Started", Status@row = "In Progress", Status@row = "Delayed"), [End Date]@row <= TODAY(30)), 1, 0))
Then you can only display rows where the box is checked, it will display items (tasks & parent rows) where due date is in the next 30 days.
Hope it helped!
Answers
-
Adding to this question. I want a Check Box column to check IF the End Date (title of column) is within 30 days of TODAY and the STATUS is In Progress, Not Started, or Delayed. My formulas are not working.
-
Hello @Leann Gibson ,
I was able to craft a formula that should fit this scenario. The formula I used was: =IF(AND(OR(Status@row = "Not Started", Status@row = "In Progress", Status@row = "Delayed"), [End Date]@row < TODAY(-30)), 1, 0)
Here is a Screenshot of this working with your chosen statutes, as well as a few different to demo the behaviour of the formula:
Let me know if you have any questions!
Regards
Sean
-
If you want to have the parent row displayed in the report, @Sean Morgan won't be enough I believe. It's perfectly fine for children rows, but Parents will remain out of it if there due date is in more than 30 days.
In the parent row you're gonna need to have this formula:
=IF(COUNTIF(CHILDREN(),1)>0,1,0)
This will check the box if any of the children is checked.
Now, if you want to automate the formula to be efficient on both praent and children rows, Use an helper column to determine the rank of the row:
Rank Column: =COUNT(ANCESTORS())+1
Then in your checkbox column:
=IF(Rank@row=1, IF(COUNTIF(CHILDREN(),1)>0,1,0),IF(AND(OR(Status@row = "Not Started", Status@row = "In Progress", Status@row = "Delayed"), [End Date]@row <= TODAY(30)), 1, 0))
Then you can only display rows where the box is checked, it will display items (tasks & parent rows) where due date is in the next 30 days.
Hope it helped!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!