Discussions

Expand all | Collapse all

AdjustMonth Bug or Not?

  • 1.  AdjustMonth Bug or Not?

    Posted 07-17-2019 19:09
    I'm still learning the ins-and-outs of QuickBase but do have extensive programming and database experience.

    Today I learned that some of my calculated date fields are NOT yielding the results I expected.   The issue is related to AdjustMonth().

    The docs:
    https://login.quickbase.com/db/6ewwzuuj?a=dr&r=be&rl=3
    "Returns the date which is m months after the given date d, with the same day number. If the day doesn't exist in that month, the last day of that month is returned."


    Thus I'd expect...

    AdjustMonth( Date( 2019,06,01 ) , 1 ) to return 2019/07/01 -- one month forward.   Works as expected.


    AdjustMonth( Date( 2019,06,30 ) , 1 ) to return 2019/07/30 -- one month forward.   Works as expected.

    AdjustMonth( Date( 2019,06,31 ) , 1 ) to return 2019/07/31 -- one month forward.   But no.   It decided July should only have 30 days.   I always get back 2019/07/30 which is not what the docs lead me to believe is correct.


    This is a "same day of the next / previous month... months...etc." not an "add/subtract 30 days" function, correct?

    Thoughts?  Advice?


    This is really messing with my ability to generate valid reports for clients that have a billing date on the 31st of a month.








  • 2.  RE: AdjustMonth Bug or Not?

    Posted 07-17-2019 19:14
    Since June only has 30 days; it is probably reading your 2019,06,31 as 2019,06,30 and that is why you are getting an adjustmonth result of 07/30/2019 instead of the 31st like you were expecting.


  • 3.  RE: AdjustMonth Bug or Not?

    Posted 07-17-2019 19:15
    I'm likely not a bug - but rather 6/31/2019 isn't an actual date - so it kicks it back to the actual day of the month - 6/30 and adjusts one month in the future accordingly. 

    You can do a LastDayOfTheMonth() formula to catch exceptions like that though. So LastDayOfMonth(AdjustMonth(Todate("6/30/2019")))


    Chayce Duncan | Director of Strategic Solutions
    (720) 739-1406 | chayceduncan@quandarycg.com
    Quandary Knowledge Base


  • 4.  RE: AdjustMonth Bug or Not?

    Posted 07-17-2019 19:16
    I was just updating my post to suggest using the LastDayOfMonth() formula with the AdjustMonth function as well... Chayce beat me to it!