Question

I have a column called > Investment start date which contains the date on which SIP was registered. Say for example 06-05-2023.
Now, I want to create a new column called next Investment date. This column will be the next date when my Investment is supposed to be deducted(mostly in monthly intervals). In this example - 06-06-2023.

Furthermore, I want this next Investment date to be updated automatically once 06-06-2023 is crossed. It should then be updated to 06-07-2023.

Please suggest a workaround since I cannot find any way to dynamically update dates each time.

Answer 1

There is no way to update dates dynamically, you have 2 options.

1- Create 12 date columns for the 12 months, if you enter 06-01-2023 in the first column, you will need to enter the sequential for every month by hand.

Once you have all columns with dates, you will need to create a new formula column to check dates.

Formula: Check when is now and show me the next month date. So, if now it is May and now its after the date added in May, the formula should show the date from the June column.

That formula needs the complete year to compare dates and know what to show.

Example: If now is May, then show June, if now is June, then show July and so on with the complete year.

Not easy and not quick.

2- Use an external tool like Make or Zapier.

Create an automation that checks the date, adds 30 days and updates the date column. This can be adjusted with Make and Zapier date tools to meet your needs.

That will work with just one date column as you have it now.

Hope that helps.