Formula to add today's date when ALL checkboxes are done.
I have a list of tasks and each task has 5 child tasks. All have separate due dates, they have a completion check box and a completed date. I have an easy formula in the completed cell to auto-populate the date when the completed box is checked and it works great! =IF(Complete@row = 1, TODAY())
What I'm trying to do is put a formula in each Parent row, in the completed date cell that will auto-populate the date when all of the children boxes are checked. Is that possible? Column names are simple, the check box is "Complete" and the date column is "Completed Date".
Thanks so much!
Best Answer
-
The issue with using the TODAY function is going to be that it will update to today's date every time the sheet is opened.
If you have access tot eh Record A Date automation, I suggest using that to record the date when the box is checked.
To automate checking the parent row box, you would use...
=IF(COUNTIFS(CHILDREN(), "") = 0, 1)
Answers
-
The issue with using the TODAY function is going to be that it will update to today's date every time the sheet is opened.
If you have access tot eh Record A Date automation, I suggest using that to record the date when the box is checked.
To automate checking the parent row box, you would use...
=IF(COUNTIFS(CHILDREN(), "") = 0, 1)
-
@Paul Newcome Thank you so much for this information. The above formula did not work for me. Can you tell me what the (), "" is for and why use COUNTIF?
Also, if I want a since row to auto-populate the date complete would you not use: =IF(Complete@row = 1, TODAY()) Would the date change every time the sheet is opened?
-
=IF(COUNTIFS(CHILDREN(), "") = 0, 1)
We use a COUNTIFS to count how many are blank (which is the "" portion), then say that if that count is equal to zero meaning no children are blank, then check the box.
The issue with using the TODAY function is going to be that it will update to today's date every time the sheet is opened. That is why I suggest the Record a Date automation instead.
-
@Paul Newcome Thank you! What is the record a date automation for a single cell when the complete box is checked?
-
It is an Automation located in the same place where you find the Alert and Update Request automations.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!