Re: Recurring Monthly Due Date Formula Help

Manny_Vergara · ‎May 24, 2022

I have the following functioning formula set up to generate a MM/DD/YYYY response based on the input from a number in the {Day of Month Due} column:

IF(
{Day of Month Due},
DATETIME_PARSE(
DATETIME_FORMAT(
TODAY(),
‘M’
)

& “/” &
{Day of Month Due}
& “/” &

  DATETIME_FORMAT(
     TODAY(),
     'YYYY'
  ),

‘L’
)
)

Result:

My goal is to make the formula generate the following month’s due date once the due date has passed.

For example:
If the {Day of Month Due} is 25, then the due date is 5/25/2022. Then, once the calendar date reaches 5/26/2022, I’d like for the formula to show 6/25/2022.

If anyone can give me some guidance on how to create this, that’d be great! Thanks in advance.

kuovonne · ‎May 24, 2022

You will also need to take into account the fact that different months have a different number of days.

Manny_Vergara · ‎May 24, 2022

So, I’ve gotten this far with the help of Colleen at Airtable Support (thanks Colleen!).

IF({Day of Month Due},IF(AND(DATETIME_FORMAT({Today},‘M’)=2,OR({Day of Month Due}=29,
{Day of Month Due}=30,{Day of Month Due}=31)),DATETIME_PARSE(DATETIME_FORMAT({Today},‘M’)& “/28/” &
DATETIME_FORMAT({Today},‘YYYY’),‘L’), IF(AND(OR(DATETIME_FORMAT({Today},‘M’)=4,
DATETIME_FORMAT({Today},‘M’)=6,DATETIME_FORMAT({Today},‘M’)=9,DATETIME_FORMAT({Today},‘M’)=11),
{Day of Month Due}=31),DATETIME_PARSE(DATETIME_FORMAT({Today},‘M’) & “/30/” & DATETIME_FORMAT({Today},
‘YYYY’),‘L’), IF(DATETIME_PARSE(DATETIME_FORMAT({Today},‘M’) & “/” & {Day of Month Due} & “/” &
DATETIME_FORMAT({Today},‘YYYY’),‘L’)>={Today},DATETIME_PARSE(DATETIME_FORMAT({Today},‘M’)
& “/” & {Day of Month Due} & “/” &
DATETIME_FORMAT({Today},‘YYYY’),‘L’),
DATETIME_PARSE(DATETIME_FORMAT(DATEADD({Today},1,‘month’),‘M’)
& “/” & {Day of Month Due} & “/” &
DATETIME_FORMAT({Today},‘YYYY’),‘L’)))))

The issue I’m running into now is when we cross into the next year, as Vivid-Squid pointed out. When the current date is in December '22, it makes the next due date in January '22. Once the current date reaches January '23, then it makes the next due date in February '23. The only problem seems to be when we are in between Dec/Jan 22/23. See below.

In the actual database, {Today} is just a TODAY() formula. I changed it to a date input for testing purposes.

Any suggestions as to how to resolve the year end transition?

kuovonne · ‎May 24, 2022

Welcome to the Airtable community!

Yikes! That is a monster of a formula. And if you need to add logic to deal with the December to January transition, it will have to be much longer.

Try something like this …

IF(
    {Day of Month Due},
    IF(
        {Day of Month Due} >= DAY(TODAY()),
        DATETIME_PARSE(
            MONTH(TODAY()) & "-" & {Day of Month Due} & "-" & YEAR(TODAY()),
            'M-D-YYYY'
        ),
        DATEADD(
            DATETIME_PARSE(
                MONTH(TODAY()) & "-" & {Day of Month Due} & "-" & YEAR(TODAY()),
                'M-D-YYYY'
            ),
            1,
            "months"
        )
    )
)

Here’s the logic. If the desired day is greater than or equal to today’s day, parse the date using the current month, the desired day, and the current year. Otherwise, add one month to the previous result.

And don’t give yourself any due days past the 28th.

Vivid-Squid · ‎May 24, 2022

Yes, that is a monster of a formula.

Here is my attempt to address the year roll over, thinking since you are only advancing one month you only need to look for the month to be 12, then add a year if true. Otherwise its the current year.

IF(MONTH(TODAY())=12,YEAR(TODAY())+1,YEAR(TODAY()))

Charlotte_Vosse · ‎Jun 03, 2022

I’m trying to do this but with a recurring date of every 6 months based off an original date without creating a new record each time, just replacing the date every 6 months.

For example - if the date is set for Jan 1st 2022, I want the recurring date of June 1st 2022, then Jan 1st 2023, etc all in the same record. Ideally it would replace the new date (ex. June 1st) every 6 months based on the original date.

So far I have used: DATEADD({Start date}, 6, ‘month’) but this only gets me one date and I’m not sure how to go beyond that.

kuovonne · ‎Jun 08, 2022

Setup an automation for the condition of the due date being in the past. Then update the record by updating the editable due date to the value in the formula field.