Adding days with formula

Platform discussions
1

Hello there, I have a questions

So the below formula

If(and({call status reason}=“No answer”,{BFS Date}=“”),format_date(add_days({Date of call},30,0)))

This works fine

It does add 30 days on {date of call} if {BFS Date} is empty

Now I want to add 30 days on {BFS Date}, if the {BFS Date} contains a date instead of adding on {Date of Call} even tho {Date of call} contains a date.

How can I have both formulas to work? Thank you in advance

2

Hi Izzy,

I couldn’t get your formula to work at all actually, so I’m surprised that it worked for you!

Nevertheless, I’ve interpreted your requirements and created this, which I believe is what you’re looking for.

Bear in mind that you may need to adjust the date format—I couldn’t see one in your original formula, so I’m not sure what your preferred format would be.

IF(
    {call status reason}="No answer",
    FORMAT_DATE(
        ADD_DAYS(
            IF(
                {BFS Date}="",
                {Date of call},
                {BFS Date}
            ),
            30
        ),
        "MMM DD, YYYY"
    ),
    ""
)
4

Hi @FrancisElliott,

I edited the first formula I sent. I must have added some extra function to it when I pasted it here.

The formula you sent worked perfectly.
Thank you for that

Other question is
I want to add 1 day on {BFS Date}, if the {BFS Date} contains a date even though {Date of call} contains a date, but add 30 days on {Date of call} if {BFS Date} is empty.

And what if I have multiple {call Status reasons}, how would the calculation change?
Let’s say

{call status reason}=“Answered”

and I want to add 3 days instead of 30 days for that specific call reason. so the calculation works for both no answer & answered call reason status.

Could you help me with that?

Thank you

1 Like
5

Hi Izzy,

I have interpreted your requirements. I didn’t fully get certain situations. Here is how it works now:

  1. BFS Date exists (in this case, adds 1 day to BFS Date)
  2. BFS Date does not exist and call Answered (in this case adds 3 days to Date of call)
  3. BFS Date does not exist and call No answer (in this case adds 30 days to Date of call)

Consider also that as formulas become more complex, it may be more manageable to separate the logic into different columns. i.e. you can have a “Days to Add” column, which takes care of that part of the logic and then a separate column which decides which date to use.

Here are two formulas. Both have effectively the same function, but the hierarchy is different. In the first, it would be easier to create specific custom logic for different statuses. In the second, which is more simple, it would be easier to change the number of days only for different statuses.

I’d recommend familiarizing yourself with the functions and how monday.com formulas work if you’re planning to continue maintaining your system by yourself.

FORMAT_DATE(
    SWITCH(
        {call status reason},
        "No answer",
        IF(
            {BFS Date}="",
            ADD_DAYS(
                {Date of call},
                30
            ),
            ADD_DAYS(
                {BFS Date},
                1
            )
        ),
        "Answered",
        IF(
            {BFS Date}="",
            ADD_DAYS(
                {Date of call},
                3
            ),
            ADD_DAYS(
                {BFS Date},
                1
            )
        ),
        ""
    ),
    "MMM DD, YYYY"
)

FORMAT_DATE(
    IF(
        {BFS Date}="",
        ADD_DAYS(
            {Date of call},
            SWITCH(
                {call status reason},
                "No answer",
                30,
                "Answered",
                3,
                0
            )
        ),
        ADD_DAYS(
            {BFS Date},
            1
        )
    ),
    "MMM DD, YYYY"
)
1 Like
6

Hello, @FrancisElliott

This formula worked brilliantly.

Thank you for your help.

And, yes, I will familiarize myself with the functions. I will do that, for sure.

Thank you once again.

1 Like