Skip to main content
Question

How can I increment a date field based on it's own value?

  • January 11, 2017
  • 0 replies
  • 100 views


I have a simple application (1 table) to track recurring tasks.  Each task has a Due-Date, Last-Completed-Date and a Frequency (monthly, quarterly, etc).  When a task gets completed, I want to increment the Due-Date based on the Frequency.  I know how to create the ?logic? for a formula to do this, by creating a formula in the Due-Date Field such as If([Due-Date] < [Last-Completed-Date], AdjustMonth([Due- Date], 1)), however this creates a Formula error ? ?A formula may not contain a reference to itself directly or indirectly through another formula?.  Can someone tell me how to get around this error?

This topic has been closed for replies.

Forum|alt.badge.img+7
  • DavidJung
  • Registered
  • January 11, 2017
Could you use a form rule to copy the completed date to a plain date field, then use that plain date field in your formula?
Similar to what Dave suggests above, I suggest a data entry field for the user to enter when they have completed the task.  Then use a formula to calculate the next reminder date.  That way the program will continue to remind them of past due incomplete tasks.  The data entry field could also be updated with a formula url button to set a date to Today.
  • KraigKraig
  • Author
  • Registered
  • January 12, 2017
Thanks for the feedback.  I probably wasn't clear enough with my original post.  The Due-Date is a calculated date to indicate when the task is next due (let's say 1/20/17).  The Last-Completed-date is a date field that the user updates when they complete the task.  Once they update the Last-completed-date (let's say to today 1/12/17), I need to increment the Due-Date field based on the frequency (monthly for example) which would then change the Due-Date to 2/20/17.  Does that make sense?
This can be done, but the formulas are finicky and you will need different formula based in the repeating cycle (weekly, monthly, quarterly, semi annually, annually). Contact me off line via the information in my profile if you want one on one assistance with this.  The other factors is that you need a start date for each reminder, so it knows its anniversary date for the next reminder.  I.e. The next reminder is not 1 month after the last one was completed, it will be the next monthly anniversary that is due.

I did an app for this once which I could show you, where all the future reminde rcords were created a future child records to the main Compliance Requirement record.  It works well.
  • KraigKraig
  • Author
  • Registered
  • January 12, 2017
Thanks again.  I think I managed to make this work based on your first suggestion with a regular Date field (not a Formula Date Field), and Form rules to increment my Due Date.  I think the only issue I have with this solution is that those form rules don't fire when a user uses the grid edit function, otherwise I think the problem is solved.  Thanks again for all of the assistance.
Terms & ConditionsAccessibility statement