Formula to add today's date when ALL checkboxes are done.

Andrea Westrich

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!

Paul Newcome

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

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)

Andrea Westrich

@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?

Paul Newcome

=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.

Andrea Westrich

@Paul Newcome Thank you! What is the record a date automation for a single cell when the complete box is checked?

Paul Newcome

It is an Automation located in the same place where you find the Alert and Update Request automations.