Can a formula be entered into a date field and get it to stick

Bolshoi

I have a sheet that tracks consultations. These are normally each row represents a single consultation. But occasionally we have a workshop where it takes several row to capture the consultations. We are using child / parent relationships to capture the rows related to the workshop. We use the top row as the parent and indent the corresponding rows beneath it to make them child rows.

The parent / child rows are identical as far as columns and the information captured. However, once a row becomes a child row we would like the date fields to be set to the dates in the parent row. I am able to do that by putting this formula in the date fields …

=IF(CRow@row = 1, PARENT(FromDate@row))

=IF(CRow@row = 1, PARENT(ToDate@row))

=IF(CRow@row = 1, PARENT(CC2Date@row))

The CRow is a a checkbox field that is checked once the row is indented to make it a child.

My issue is that I cannot get the formulas to stick. What I mean is I can put these formulas in all the associated date fields for the entire table. Since most rows are not going to be child rows the normal action is for someone to enter a date for each of the 3 date fields on the record. If they do this, then the formula is no longer included in that date field. So if later it is indented to become a child row there is no formula to act on. Or if someone enters a date and then removes a date in the normal field the formula is also removed. Is there a way I can do this and make the formulas persistent if the date is removed?

Any ideas to help me with this. I have tried a lot of things and work-arounds but none see to work.

Andrée Starå

Hi Bolshoi,

There are a couple ways to solve it.

• Have a section/row that you copy from.
• Add so-called helper columns to make sure the formulas needed are kept intact.

Would any of those options work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Bolshoi

I tried the helper columns but couldn't get the formula I entered to work. I tried using a checkbox column and when that didn't work I tried adding a normal text column.

Maybe you could provide an example using the values I put in the original message?

Thank you for any help you can offer

Andrée Starå

Happy to help!

In my example below I have used four columns.

• Date Shown = Helper column - Date Show from Manual Date or from Date from Parent
• Manual Date = Manually entered date (This is the one you'd enter normally)
• Date from Parent = Helper column - that shows the date from the Parent in the Manual Date column (if it's a child row)
• Primary = Your main column

Formula to add to the Date Shown Column

Try something like this.

=IF(ISDATE([Date from Parent]@row); [Date from Parent]@row; [Manual Date]@row)

The same version but with the below changes for your and others convenience.

=IF(ISDATE([Date from Parent]@row), [Date from Parent]@row, [Manual Date]@row)

Formula to add to the Date from Parent Column

Try something like this.

=IF(COUNT(CHILDREN(Primary@row)) > 0; [Manual Date]@row; IF(COUNT(ANCESTORS(Primary@row)) > 0; PARENT([Manual Date]@row)))

The same version but with the below changes for your and others convenience.

=IF(COUNT(CHILDREN(Primary@row)) > 0, [Manual Date]@row, IF(COUNT(ANCESTORS(Primary@row)) > 0, PARENT([Manual Date]@row)))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Make sense?

Did it work?