Discussions

 View Only
  • 1.  Formula to display upcoming month (and year) referencing the day from a field

    Posted 09-26-2020 17:39
    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
    ------------------------------


  • 2.  RE: Formula to display upcoming month (and year) referencing the day from a field

    Posted 09-26-2020 17:56
    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
    ------------------------------



  • 3.  RE: Formula to display upcoming month (and year) referencing the day from a field

    Posted 09-26-2020 18:43
    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
    ------------------------------



  • 4.  RE: Formula to display upcoming month (and year) referencing the day from a field

    Posted 09-26-2020 18:45
    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
    ------------------------------