I am trying to get the parent row date to auto update to next cleaning interval date

Cassie Walker

Hi All,

We are trying to capture our equipment cleaning logs using Smartsheet. I have created a sheet with hierarchy levels for each piece of equipment and all the cleaning dates needed for the year. What I would like to do is have the date in the parent row be the next date due, based on whether or not the Last cleaned date is filled. Is this possible?

I got this to work with an IF statement for the equipment with 6 month interval as there are only two dates, but was unsure how to make this work with more 'conditions'. I have inserted an example below for one piece of equipment with 3 month cleaning intervals for which a standard IF statement won't work. The highlighted cell is the parent cell. So in this instance I would want it to recognize that the Due date of 3/13 was met (cleaned on cell has a value), so I would like the parent line to state 6/13 as that is the next date due. Any help would be greatly appreciated!

Thanks!

Cass

Paul Newcome

Try something like this...

=INDEX(CHILDREN(), COUNTIFS(CHILDREN([Cleaned On Date:]@row), ISDATE(@cell)) + 1)

Basically we count how many dates have been entered and add 1 then use this number to tell the INDEX function which child row to pull the date from.

Paul Newcome

Duh. Of course it would throw an error. The range to pull from doesn't include the next parent row. Coffee time. Haha.

Give this little change a shot...

=IFERROR(INDEX(CHILDREN(), COUNTIFS(CHILDREN([Cleaned On Date:]@row), ISDATE(@cell)) + 1), "Complete")

Paul Newcome

Try something like this...

=INDEX(CHILDREN(), COUNTIFS(CHILDREN([Cleaned On Date:]@row), ISDATE(@cell)) + 1)

Basically we count how many dates have been entered and add 1 then use this number to tell the INDEX function which child row to pull the date from.

Cassie Walker

@Paul Newcome this is EXACTLY what I needed. Thank you!!!!!

Paul Newcome

Happy to help! 👍️

My only concern would be when the final child row date is entered. Then it will pull the parent row data for the section below it.

There may be a way to have someone manually enter a "Last Cleaned" date and use a formula to generate the next Due Date based on the frequency and then set up another sheet as the target of a Copy Row Automation. The Copy Row could be triggered whenever the "Last Cleaned" date changes and it will copy that static data over to the other sheet. This will allow you to track the historical data for quite some time without having to worry about running out of child rows. Your main working sheet would only have a single row for each task.

I have also recently helped out a few people with a formula that allows you to add n months to a date, so you would be able to use a generic formula in the Due Date, enter a number in a column for whatever month interval for that column and use that for every task instead of having to write formulas specifically for 3 months and a different one specifically for 6 months.

What matters is that you have a working sheet. I figured I would just throw some other ideas out there too.

Cassie Walker

It doesn't end up pulling the parent data for the row below, but it does stay invalid. I almost wonder if it could be treated with a way to then change it so that if it can't pull a value and will trigger an invalid if it would say complete instead. I'll have to play around with it.

Paul Newcome

Duh. Of course it would throw an error. The range to pull from doesn't include the next parent row. Coffee time. Haha.

Give this little change a shot...

=IFERROR(INDEX(CHILDREN(), COUNTIFS(CHILDREN([Cleaned On Date:]@row), ISDATE(@cell)) + 1), "Complete")

Cassie Walker

Perfect!! Thanks! I too need more caffeine!

Paul Newcome