How to create a field for Next Date Due

  • 0
  • 1
  • Question
  • Updated 10 months ago
  • Answered
How do I create a field that calculates a "next due date" based off an initial start date and todays date (in 3 months after start date, in 6 months after start date, in 12 months after start date). I want the field to show the next due date that occurs after todays date.   This is for scheduling medical tests that need to be done at specific intervals after the date of diagnosis.  I'd appreciate any help with this.  Thank you.
Photo of sheree brown

sheree brown

  • 210 Points 100 badge 2x thumb

Posted 11 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Try this

LastDayOfPeriod(Today(), Days(365/12*3), [Reference date]) + Days(1)

The function LastDayOfPeriod is described here
https://login.quickbase.com/db/6ewwzuuj?a=dr&r=bn&rl=dvd
Photo of sheree brown

sheree brown

  • 210 Points 100 badge 2x thumb

This would work if my due dates had the same duration interval, however, they are variable.  Every 3 months for 2 yrs, then every 6 months, etc. 

After searching through other posted solutions, I created a new field for each of the upcoming due dates [ _ Month testing due], based off my starting date [Date of Program Entry] by using the following example for the [3month testing due date]:

AdjustMonth([Date of Program Entry], 3)

I plan to hide all these fields in my form.

Once all those fields were created, I created another field [Next Due Date] and used the following formula to show the next due date occurring after the month of today's date.

Min(
  If([3 Month Testing Due] >= FirstDayOfMonth(Today()), [3 Month Testing Due], null),
  If([6 Month Testing Due] >= FirstDayOfMonth(Today()), [6 Month Testing Due], null),
  If([9 Month Testing Due] >= FirstDayOfMonth(Today()), [9 Month Testing Due], null),
  If([12 Month Testing Due]>= FirstDayOfMonth(Today()), [12 Month Testing Due], null),
  If([15 Month Testing Due] >= FirstDayOfMonth(Today()), [15 Month Testing Due], null),
  If([18 Month Testing Due] >= FirstDayOfMonth(Today()), [18 Month Testing Due], null),
  If([21 Month Testing Due] >= FirstDayOfMonth(Today()), [21 Month Testing Due], null),
  If([24 Month Testing Due] >= FirstDayOfMonth(Today()), [24 Month Testing Due], null),
  If([30 Month Testing Due] >= FirstDayOfMonth(Today()), [30 Month Testing Due], null),
  If([36 Month Testing Due] >= FirstDayOfMonth(Today()), [36 Month Testing Due], null),
  If([42 Month Testing Due] >= FirstDayOfMonth(Today()), [42 Month Testing Due], null),
  If([48 Month Testing Due] >= FirstDayOfMonth(Today()), [48 Month Testing Due], null),
  If([54 Month Testing Due] >= FirstDayOfMonth(Today()), [54 Month Testing Due], null),
  If([60 Month Testing Due] >= FirstDayOfMonth(Today()), [60 Month Testing Due], null))


It seems to be working correctly. Maybe there was an easier way?

Thank you, I use your posted solutions frequently.

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Ok, thx for the feedback and letting know that you found a solution.