Forum Discussion

ChrisFaye1's avatar
ChrisFaye1
Qrew Cadet
5 years ago

Formula to display upcoming month (and year) referencing the day from a field

Hi, 

Long story short I'm trying to create a formula that, when a month-to-month checkbox is checked, the field will reference another date field, keep the day, but adjust the month and year to be the upcoming month(year assumed). 

Probably easier to show a few examples...

If ([Month to Month]=true, 

Then reference [End Date], If [End Date] < Today, then assume the next month (year assumed) for the [Reported End Date]. 

Ex 1: End Date many months passed
Month to Month = true
Today's Date = 9/26/20
End Date = 5/05/20
Reported End Date: 10/5/20

Ex 2: End Date recently passed & turn of the year
Month to Month = true
Today's Date = 12/30/20
End Date = 12/10/20
Reported End Date: 1/10/21

Ex 3: End Date not yet passed
Month to Month = true
Today's Date = 9/26/20
End Date = 9/27/20
Reported End Date: 9/27/20

Thank You!
Chris

------------------------------
Chris
------------------------------
  • Try this

    var date TodayPlusOneMonth = AdjustMonth(Today(),1);

    IF([Month to Month],
    Date(Year($TodayPlusOneMonth), Month($TodayPlusOneMonth), Day([End Date])))

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • ChrisFaye1's avatar
      ChrisFaye1
      Qrew Cadet
      Thank You, Mark!

      It wasn't perfect with the turnover based on today's date, but instead simply assumes the next month, but realistically that's all I need it to do so I'm all set!

      It'd be interesting to see how it might look if it were adding only if the current date had passed, but, again, this definitely works for my needs. 

      Thanks Again!
      Chris

      ------------------------------
      Chris
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        If there is a more complicated logic that you can explain, we can enhance the formula.

        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        mark.shnier@gmail.com
        ------------------------------