I have been meaning to update my due-date-setting expression editor for a while to make it provide the same numerical day but X months in the future from the current date. Previously I was effectively adding a fixed interval (months converted to seconds) but depending on the starting date and the month interval, the numerical day of the due date would vary from that.
After a bit of trial and error and inspiration from both Thursday Office Hours sessions today, I figured out that the missing expression editor function in my calculation was “interval_to_seconds” which was needed to enclose the datetime subtraction argument in order for the expression to be valid.
rounddatetime(rounddatetime(@App Info.Current Date and Time , ‘month’) + (60 * 60 * 24 * 31 * @Variable.# of Months before Due ), ‘month’) + interval_to_seconds(rounddatetime(@App Info.Current Date and Time , ‘day’) - rounddatetime(@App Info.Current Date and Time , ‘month’))
I after failing on multiple attempts, I decided to save my intermediate valid expressions to variables and then do the final expression adding the variables together…which did produce a usable result
Only after inspecting the variable and finding the latter subtraction argument resulted in an interval datatype (as opposed to a datetime datatype), did I realize the need for the “interval_to_seconds” function.