Help with a formula please!

[Jim007]

Hi 

Just wondering if anybody can help with a formula?

I'm trying to set up a task (meeting date) that can occur on a Tuesday of say the 1st week, 2nd week, 3rd week or 4th week of the month. 

So lets say the due day was  Tuesday 02/10/2024 then i'd like to set up a recurring meeting date for the 1st Tuesday of the following month. If the due date was Tuesday 08/10/2024 then it would recur on the 2nd Tuesday the following month and so on.

My head is baffled so any help would be greatly appreciated.

[Mike_AutomaticN]

Hey @Jim007!

Assuming you have a field called Due Date, and assuming that on the same record you want to see what the date for the next meeting should be, you can create a formula field called Upcoming Due Date with the following formula shown below. Furthermore, you could use the value of such field to create a new record for the upcoming meeting through an automation. 

Let me know if that works -if you find any edge case for which this formula does not work, please let me know and we'll go through it!

IF(
    {Due Date},
    DATEADD(
        DATEADD(
            DATEADD(
                DATEADD(
                    DATETIME_PARSE(
                        DATETIME_FORMAT(
                            DATEADD(
                                {Due Date},
                                1,
                                'month'
                            ),
                            'YYYY-MM-01'
                        ),
                        'YYYY-MM-DD'
                    ),
                    IF(
                        DAY({Due Date}) <= 7, 
                        0, 
                        IF(
                            DAY({Due Date}) <= 14, 
                            7, 
                            IF(
                                DAY({Due Date}) <= 21, 
                                14, 
                                21
                            )
                        )
                    ),
                    'days'
                ),
                MOD(
                    WEEKDAY({Due Date}) - WEEKDAY(
                        DATEADD(
                            DATETIME_PARSE(
                                DATETIME_FORMAT(
                                    DATEADD(
                                        {Due Date},
                                        1,
                                        'month'
                                    ),
                                    'YYYY-MM-01'
                                ),
                                'YYYY-MM-DD'
                            ),
                            'days'
                        )
                    ) + 7, 
                    7
                ),
                'days'
            ),
            0,
            'days'
        ),
        0,
        'days'
    )
)

Mike, Consultant @ Automatic Nation