AdjustMonth Bug or Not?

  • 0
  • 1
  • Question
  • Updated 1 month ago
  • In Progress
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.






Photo of cyberLance

cyberLance

  • 70 Points

Posted 1 month ago

  • 0
  • 1
Photo of QuickBasePros (Laura Thacker)

QuickBasePros (Laura Thacker), Champion

  • 6,396 Points 5k badge 2x thumb
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.
Photo of Dev Quandary

Dev Quandary

  • 64 Points
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
Photo of QuickBasePros (Laura Thacker)

QuickBasePros (Laura Thacker), Champion

  • 6,396 Points 5k badge 2x thumb
I was just updating my post to suggest using the LastDayOfMonth() formula with the AdjustMonth function as well... Chayce beat me to it!