Need help in creating Compliance sheet for customer.

Kamya Pamnani

For Compliances, the customer tracks items with their deadline dates like Income tax return 20 Sept every year, TDS payment 7th of every month, TDS return by 30th day after end of Quarter. They want once the status is completed, the date should automatically change so the reminders will be on the new dates. Please help on how to do that. The screenshot is as below:

KPH

You can create formula to express the logic you have in the "timeline" column. For example if you hold the incorporation date in the date column in row 1 you can do something like this to add 30 days to that date:

And to add a month to the date in your date column you would do this:

=DATE(YEAR(Date@row), MONTH(Date@row) + 1, DAY(Date@row))

Or a year

=DATE(YEAR(Date@row) + 1, MONTH(Date@row), DAY(Date@row))

Then you can use these within an IF formula to update the new date column only when the checkbox is checked.

=IF(Checkbox@row = 1, DATE(YEAR(Date@row) +, MONTH(Date@row) + 1, DAY(Date@row)), "")

This will enter a new date one month after the date in the date column, when the checkbox is checked (I realize now that this row in your example should have added a year but you get the idea).

However, I think you might need to change the process. Once the user checks the box to say "completed" the new date will be populated based on your formula. But what then? How does the user complete the task next time? Some of these tasks are one offs, some are monthly, some are annually. If might be better to replace the checkbox with a "last completed" date or similar rather than a checkbox.