Additional query to current IF statement

Platform discussions
1

I have this formula which works great and all I want to do is add an additional query to if and I have tried numerous things that are not working. Please can someone help.

IF(AND(DAYS(“2023-4-1”, FORMAT_DATE({Deferral Start Date})) >= 0, DAYS(FORMAT_DATE({End}), “2023-4-1”) >= 0), {Invoice £} / {Term}, 0)

This formula is in a column called F(01)Apr-2023.
We have added a number column called M(01)Apr-2023 next to it.

If someone enters a number in the M(01)Apr-2023 then we want the F(01)Apr-2023 column to return 0. If nothing is entered in the M(01)Apr-2023 then the formula does the calculation above.

Thanks in advance for your help.
Lisa

2

Hi Lisa,

Try:

IF({M(01)Apr-2023}=0,0,IF(AND(DAYS("2023-4-1", FORMAT_DATE({Deferral Start Date})) >= 0, DAYS(FORMAT_DATE({End}), "2023-4-1") >= 0), {Invoice £} / {Term}, 0))

Want to take your formulas to the next level? Try the Advanced Formula Booster, the app that reinvents formulas in monday.

  • Create formulas without using the Formula column (and avoid its limitations)
  • Build formulas involving data from the previous item, the next item, the sub-items, the parent item, even items in the same group or the same board.
  • In one formula, update multiple columns from multiple items.

Check our blog for real use cases.

3

Thank you I already tried that but as soon as I enter a number in the M(01)Apr-2023 column the F(01)Apr-2023 doesnt change to zero unless I actually put a zero in M(01)Apr-2023. If I put any other number in then the formula above does the calculation

image

image

image

I suppose I am trying to say if it is empty then do the calculation but if there is any number at all, including zero in the M(01)Apr-2023 column then return zero in the F(01)Apr-2023 column.

Apologies hope that makes sense.

4

Then try:

IF({M(01)Apr-2023}>0,OriginalFormula,0)

What if we could break free of the Formula column? and write formulas that update any type of columns? What if a formula could update multiple columns at once? This is possible with the Advanced Formula Booster app.

5

Hi again. Sorry I tried that also. I am just checking and as it has something to do with the Deferral start date I am having to use

IF({M(01)Apr-2023}>=1,0,IF(AND(DAYS(“2023-4-1”, FORMAT_DATE({Deferral Start Date})) >= 0, DAYS(FORMAT_DATE({End}), “2023-4-1”) >= 0), {Invoice £} / {Term}, 0))

This shows correct now when I change the deferral date to start on the 1st June for this line item. Example below by adding in a number:

image
image769×72 5.39 KB

If I put zero or nothing in the Manual then it does the calculation so I think this is now correct. I will try with others but thank you for your help :slight_smile:

image
image762×73 5.5 KB

6

Final calculation that works for my issue, in case others need to try.

IF(OR({M(01)Apr-2023}>=1,{M(01)Apr-2023}<>0),0,IF(AND(DAYS(“2023-4-1”, FORMAT_DATE({Deferral Start Date})) >= 0, DAYS(FORMAT_DATE({End}), “2023-4-1”) >= 0), {Invoice(s) £} / {Term}, 0))