- Formula Column - Need Help Adjusting Deadline Formula to Account for Suspensions

Hello fellow monday users!

I’m currently facing a challenge with the formula column on our project management board and would appreciate any insights or advice from the community.

The Goal: We need to calculate a project deadline date based on the initial payment date, but our projects can occasionally be suspended, which should push back the deadline accordingly. The challenge is to adjust the deadline dynamically, depending on whether a suspension period has been defined or not.

The Problem: Our current formula needs to account for three scenarios:

  1. When only the payment date is available → (69 days from payment date)
  2. When the payment date and the suspension start date are available, but the suspension end date is not yet defined. → (Deadline suspended)
  3. When both suspension start and end dates are available. → (adjusted Deadline date (69 days plus suspension period)

The desired output should adjust the project deadline based on the suspension period, and if the suspension is ongoing (i.e., no end date), the status should show as “Deadline Suspended.”

Current Formula Setup: Here’s what we’ve been working with:

IF({1. TR Zahlungseingang}="",
    "Zahlungseingang erforderlich",
    IF(AND({Datum Deadline ausgesetzt}<>"", {Datum Deadline fortgesetzt}=""),
        "Deadline ausgesetzt",
        IF(AND({Datum Deadline ausgesetzt}<>"", {Datum Deadline fortgesetzt}<>""),
            FORMAT_DATE(
                ADD_DAYS(
                    {1. TR Zahlungseingang},
                    69 + DAYS_DIFF({Datum Deadline fortgesetzt}, {Datum Deadline ausgesetzt})
                ),
                "DD/MM/YYYY"
            ),
            FORMAT_DATE(
                ADD_DAYS({1. TR Zahlungseingang}, 69),
                "DD/MM/YYYY"
            )
        )
    )
)

Issues Faced:

  • The formula does not work and I have no idead why
  • Difficulty in ensuring that the formula dynamically adjusts dates based on potential ongoing suspensions.

I’ve attempted various modifications to this formula, but none seem to perfectly fit the requirements or operate without error.

Request for Assistance: Does anyone have experience with similar setups or could offer advice on how to manage such dynamic date adjustments in monday? Any guidance on improving the formula or configuring the board differently would be greatly appreciated.

Thank you in advance for your help!

Hi Andreas,

Regarding your current formula, replace DAYS_DIFF by DAYS and it will work.

Regarding guidance, if you have other formulas like this to create I suggest using a 3rd-party app called Advanced Formula Boosters. It really revolutionizes creating formulas in monday.

  • The syntax editor can take up to 100 lines (=100 instructions)
  • 1 formula can update more than 1 column
  • Formulas can update items besides the current item, ie. previous, next, sub-items, parent items, items in the same group, in the same board, in other boards
  • You can use variables, ie. store part of the calculation in a variable to reuse it later.
  • etc.
IF({1. TR Zahlungseingang}="",
    "Zahlungseingang erforderlich",
    IF(AND({Datum Deadline ausgesetzt}<>"", {Datum Deadline fortgesetzt}=""),
        "Deadline ausgesetzt",
        IF(AND({Datum Deadline ausgesetzt}<>"", {Datum Deadline fortgesetzt}<>""),
            FORMAT_DATE(
                ADD_DAYS(
                    {1. TR Zahlungseingang},
                    69 + DAYS({Datum Deadline fortgesetzt}, {Datum Deadline ausgesetzt})
                ),
                "DD/MM/YYYY"
            ),
            FORMAT_DATE(
                ADD_DAYS({1. TR Zahlungseingang}, 69),
                "DD/MM/YYYY"
            )
        )
    )
)

it still gives an Error