Your fundamental tools are AdjustMonth, FirstDayOfMonth and LastDayOfMonth.
If( [TMB Renewal] <= LastDayOfMonth(AdjustMonth(Today(),1)) and
[TMB Renewal] >= FirstDayOfMonth(AdjustMonth(Today(),1)), true, false )
This checks of the TMB Renewal is in next month and will dynamically change every month.
Don,
Thank you so much for your reply. I tried that code in a formula text field, is that correct? And I received this error. "Expecting text but found bool"
Also if I get this working can I add the other date fields in the same formula? I am hoping to see all monthly due-dates across all renewal fields.
The formula I gave you results in a True or False answer to the question, "Is it Due Next Month" and specifically the TMB Renewal. That field type should be Formula Checkbox.
If you just want to see the dates you can make a Formula Text Field but need to change the outputs something like this.
If( [TMB Renewal] <= LastDayOfMonth(AdjustMonth(Today(),1)) and
[TMB Renewal] >= FirstDayOfMonth(AdjustMonth(Today(),1)), ToText(
[TMB Renewal] ), "" )
This would show the date if it is next month and nothing if it is not. There are unlimited options. I have a client that has field like this that checks on supplier payments. All the logic is about 400 rows. You should not need anything that long.
