Delay next due date update by 5 days

[Pidah_Tnadah]

Hi guys,

I have a next due date formula that updates to the next month when it is due but i want to delay the due date to change 5 days after the due date, below is the formula i use.

DATEADD({Contract starts}, DATETIME_DIFF(TODAY(), {Contract starts}, “months”) + 1, “months”)

Thanks in advance

[Justin_Barrett]

There are a couple of ways to pull this off. One is to wrap another DATEADD() function around the existing one and add five days. However, another option is to switch from months to days, and add 35 days total:

DATEADD({Contract starts}, 5 + (DATETIME_DIFF(TODAY(), {Contract starts}, "months") + 1) * 30, "days")

With longer months (31 days) it might not produce the exact same output as the “months” method, but you can always tweak the number of added days as you wish.

[Pidah_Tnadah]

Thanks for the reply , tried the formula and it adds an extra five days to the due date, what i was hoping is the due date remains but changes to the next month 5 days later.

[Justin_Barrett]

I’m not sure that I understand. Could you give a specific example?

[Pidah_Tnadah]

Yea sure, so a tenants rent is due every month and the rent due date is updated to the next month on the day it is due from March 11,2021 to April 11,2021 etc I would like to still see the due date on the day the rent is due which is March 11,2021 and it still remains March 11, 2021 for 5 days and then gets updated to April 11, 2021.

[Justin_Barrett]

Ah, gotcha. That’s definitely more clear, but also more challenging. At first I thought I would need a day or two to mull it over, but inspiration hit and I got it working. (I start the formula with the IF() function just to prevent error messages with no contract date entered, and I split it across multiple lines because it got too hard to keep track of otherwise.)

IF(
    {Contract Starts},
    DATEADD(
        {Contract Starts},
        DATETIME_DIFF(
            TODAY(),
            {Contract Starts},
            "months"
        ) + (
            TODAY() > DATEADD(
                DATEADD(
                    {Contract Starts},
                    DATETIME_DIFF(
                        TODAY(),
                        {Contract Starts},
                        "months"
                    ),
                    "months"
                ),
                5,
                "days"
            )
        ),
        "months"
    )
)

Here’s a look at my test data (replacing TODAY() with reference to the {today} field).

[Pidah_Tnadah]

Thank you, Much appreciated. Works perfectly!!