How do I have a field called "Next Due Date" automatically update itself every month according to the Original Date?

  • 0
  • 1
  • Question
  • Updated 5 years ago
  • Answered

For example: A client comes on board 10/15/2014. I want the "Next Due Date" to be 11/15/2014. When Today'sDate becomes 11/15/2014, I want the "Next Due Date" to automatically change itself to 12/15/2014. So on and so forth.

Thanks!

Photo of NP

NP

  • 10 Points

Posted 5 years ago

  • 0
  • 1
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 4,208 Points 4k badge 2x thumb
I came up with this formula. In this case my original date is the [Date Created]. If you use a standard DATE-type field, you do not need the conversion ToDate()

var number day=Day(ToDate([Date Created]));


var number month=Month(Today());

var number year=Year(Today());

var date thismonth=ToDate(ToText($day & "/" & $month &"/" & $year));


var date nextmonth=AdjustMonth($thismonth,1);

If(Today() <= AdjustMonth(ToDate([Date Created]),1),

AdjustMonth(ToDate([Date Created]),1),

$nextmonth)

Photo of NP

NP

  • 10 Points
I'm not sure why I can't see the formula here but I was able to view it through the email notification I received. Unfortunately, it still did not work the way I'd like. If I adjust the Original Date to 08/22/2014, the "Next Due Date" field should show 10/22/2014. In November, it should show 11/22/2014, so on and so forth.
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 4,208 Points 4k badge 2x thumb
NP- I have updated my original formula which I believe will work for you.  Essentially it says that if Today() is more than 1 month past your original date, then use Todays date and add 1 month to it; always using the DAY from the original date.