Is there a way to record due dates in one cell?

Sara Ross

Requests we receive are assigned a due date. This due date may change once or several times depending on our ability to respond. Is there a way to create an automation to record the due dates in a single cell to show the progression?

Paul Newcome

Are you able to provide a screenshot of a sample (manually entered) that shows what you are wanting to achieve?

Sara Ross

I would like the history column to list all of the due dates that have been associated with the request in each row.

Paul Newcome

The only way to do this would be to capture each date change in its own column or leverage the API to look at the cell history. Otherwise your only options for viewing the changes would be to check cell history, check the Activity Log, or have users manually update this column in addition to the updated date.

There is a way to automate it, but it could very quickly reach some of the built in limits depending on how many rows you need to capture this for and how many times the date could change. The basic idea would be to use a unique id on each row then set up a copy row automation triggered when the due date changes to any value to copy the row over to another sheet. From there you would use a JOIN/COLLECT with cross sheet references to join all of the dates together based on the row's unique id. But again... Depending on how many times a single row can change and how many rows you have in the sheet (or could potentially have as the sheet grows), you could very quickly run into some of the built in limitations.

Sara Ross

Thanks for the feedback. Generally, there aren't a large number of requests with due date changes. I actually already have an automation set up to copy the rows to another sheet when the due date changes. Could you please step me through how I would then use the formula with cross sheet references to populate the history column in the original sheet? Much appreciated!

Paul Newcome

Basically it would look something like this...

=JOIN(COLLECT({Copy Sheet Date Column}, {Copy Sheet Unique ID Column}, [Unique ID]@row), CHAR(10))

Sara Ross

Cool! It worked.

Big thank you. Have a great weekend!

Paul Newcome

Happy to help. 👍️