Forum Discussion

ShereeBrown's avatar
ShereeBrown
Qrew Cadet
6 years ago

How to create a field for Next Date Due

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.
Formulas and functions

5 Replies

Sort By
    • MichaelTamoush's avatar
      MichaelTamoush
      Qrew Captain
      5 years ago
      I know this is an old thread, but if I can revive it...I need a similar thing but am not sure I understand date d or the +days(1)? In my case I have a set initial start date, and need a due date every quarter. So I think I can do LastDayOfPeriod(Date d, Days(365/12*3), [Initial Start Date]) + Days(1)

      What is the date d, and why add the 1 day?

      *Edit: Understand the date d (made it today() per this thread. Not sure of the +1 day but working for me.

      ------------------------------
      Mike Tamoush
      ------------------------------
      • DonLarson's avatar
        DonLarson
        Qrew Commander
        5 years ago
        Mike,

        What is the business case?  Are you searching for the end of the current quarter or of a different quarter based upon a date in the record?

        If you truly just need four dates for the end of each quarter
        • Mar 31
        • June 30
        • Sep 30
        • Dec 31

        then a formula which returns the closest future date will suffice.

        If your dates are more fluid, excluding weekends, holidays.... then you need a more complicated solution.  




        ------------------------------
        Don Larson
        Paasporter
        Westlake OH
        ------------------------------
  • ShereeBrown's avatar
    ShereeBrown
    Qrew Cadet
    6 years ago

    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.