Automated Date Capture Process with Recurrence

Chris Walker

I have a sheet where we track Products (per row) and we need to track a user access review every year. To do so, I want to document the Product Owner and the Product Initiation Date - from there (when new row and product initiation date is not blank), I would need to auto fill a cell (Column: Next User Access Review) with a date value one year from the product initiation date. When the one year mark hits, a notification will send to the Product Owner to complete the user access review. I would like to send an update request, where the Product Owner will respond to a question "Date User Access Review Completed" and this will write back to a cell. Additionally, I would like the sheet to add one year again to the column 'Next User Access Review' to prepare the date-based notification for next year's review.

Columns:

Product Owner | Product Initiation Date | Next User Access Review | Date User Access Review Completed |

I think I have a plan of action, but looking for some validation that I am going the right way about this. I'm happy to provide more context/clarification if needed.

Summary:

1. Date anchor (Product Initiation Date) to get the process started
2. Change cell value automation to populate 'Next User Access Review' +1 year from Product Initiation Date
3. When date reached, send update request to Product Owner AND change cell value automation to populate 'Next User Access Review' +1 year from Next User Access Review (I'm a little fuzzy here if this is possible, or if I'll need a helper column)
4. Repeat step 3

Antonie B

@Chris Walker ,

You are on the right track! I would suggest the following:

1: Keep as is

2: Have a column that will record a date (i.e Date Stamp) #. This will be updated with the "Record a Date" Automation, once at the start of the process when Product Initiation Date is added, and then once a year, when the update request is completed.

3. Next User Access Review will then automatically look at the Date Stamp and add a year (use Date Stamp]@row +365 or build the it using the DATE formula function.

Does that help?