I want to generate a new review date based off user's due date, excluding weekends

I have a “Due Date” column that the requestor fills out. Currently I use this formula “LEFT(FORMAT_DATE(SUBTRACT_DAYS({Due Date},3)),6)” to subtract 3 days from the “Due Date” and generate a new date in the “For Review” column. I would like to exclude weekends from this formula. I understand monday.com has a new formula “WORKDAYS({Date1},{Date})” to fix this but I’m not sure how to integrate this with my original formula. If anyone can help me with this it will be greatly appreciated.

Here is an implementation of one approach to subtracting working days from a date.

Right now it uses a number from the “Days to Sub” column, but you can just switch all of those out with “3” and it’ll work fine if you don’t need that flexibility.

FORMAT_DATE(
    SUBTRACT_DAYS(
        {Due Date},
        SUM(
            {Days to Sub},
            IF(
                MOD(
                    {Days to Sub},
                    5
                )
                >
                WEEKDAY({Due Date})-1,
                2,
                0
            ),
            ROUNDDOWN(
                {Days to Sub}/5,
                0
            )*2
        )
    ),
    "MMM DD"
)

With “3” as a fixed subtraction:

FORMAT_DATE(
    SUBTRACT_DAYS(
        {Due Date},
        SUM(
            3,
            IF(
                MOD(
                    3,
                    5
                )
                >
                WEEKDAY({Due Date})-1,
                2,
                0
            ),
            ROUNDDOWN(
                3/5,
                0
            )*2
        )
    ),
    "MMM DD"
)

That worked!! Thank you so much!

1 Like

Would the same be true if i wanted to subtract 1 day?

Yep. You just need to change 3 to 1 in the formula. Or use a helper column with the first formula—that way you can choose the days to subtract.

I have another board with a “Live Date” column and “Final to Marketing” column using the code below (Subtracting 1). However, if you choose a Sunday (Live Date) it still returns a Saturday date in the Final to Marketing column instead of pushing it to Friday. It works if I pick Monday, it pushes it back to Friday. I think I’m missing something.

FORMAT_DATE(
SUBTRACT_DAYS(
{Live Date},
SUM(
1,
IF(
MOD(
1,
5
)
>
WEEKDAY({Live Date})-1,
2,
0
),
ROUNDDOWN(
1/5,
0
)*2
)
),
“ddd MMM DD”
)

I see. This is related to starting on Sunday rather than the number of days subtracted. Having it behave correctly for Sundays is something like an enhancement. I’ve added it into the formula below:

FORMAT_DATE(
    SUBTRACT_DAYS(
        {Due Date},
        SUM(
            {Days to Sub},
            IF(
                MOD(
                    {Days to Sub},
                    5
                )
                >
                WEEKDAY({Due Date})-1,
                2,
                0
            ),
            ROUNDDOWN(
                {Days to Sub}/5,
                0
            )*2,
            IF(
                AND(
                    WEEKDAY({Due Date})=7,
                    {Days to Sub}>0
                ),
                1,
                0
            )
        )
    ),
    "MMM DD"
)

That worked!! Thank you!!

1 Like