IF conditions & Date Calculation Formula

Platform discussions Special workflows & use cases
1

Hi all,

Two different scenarios, very simple but I could not manage to get them working :frowning:

First is,
I am trying to create a formula where it’s supposed to calculate the days between 12/31/2022 and certain column, then divide it to 30 to take months, then multiply the result with two other columns and round the result and reflect it to the respective row.

Now I have added a condition to apply a rule where it should check if the result of the above mentioned equation is less than zero or not and reflect the data accordingly. However, I could not get the formula working properly…

IF(ROUND(SUM(DAYS(DATE(2022,12,31), {Possible Start Date})/30){Number of Employees}{Price Equivalence}) > 0,
ROUND(SUM(DAYS(DATE(2022,12,31), {Possible Start})/30){Number of Employees}{Price Equivalence}),0)

What I’m trying to achieve is very simple, if the round result between dates is negative, than reflect 0 (or any custom text) otherwise, reflect the result itself.

Second is,
The same formula, however I’ll be making it calculate results for 2023 only. Therefore I need the formula to make the calculation starting from Jan 1, 2023 and avoid any months coming from 2022. How should I modify it?

ROUND(SUM(DAYS(DATE(2023,12,31), {Possible Start})/30){Number of Employees}{Price Equivalence}
,0)

Any tips or help is much appreciated.

Thanks to all in advance!

2

@thekid

The primary issue with your formula is that monday formulas do not support implicit multiplication, e.g., {Number of Employees}{Price Equivalence}, must be changed to explicit mulitplication, like, {Number of Employees} * {Price Equivalence}.

So, ROUND(SUM(DAYS(DATE(2023,12,31), {Possible Start})/30){Number of Employees} {Price Equivalence},0) should read: ROUND(SUM(DAYS(DATE(2023, 12, 31), {Possible Start}) / 30) * {Number of Employees} * {Price Equivalence} ,0)

Jim - The Monday Man
:magic_wand: Update Magic #1 – New update enhancement toolbox
:magic_wand: Column Magic :sparkles:– The magical columns toolbox
We Create Custom Apps, Integrations & Automations for monday

3

Hi Jim,

Thanks for your reply! Formulas are actually saved as you have mentioned, the * parts just got deleted while copy pasting formulas over here. However, as is they just do not perform what I need…

4

@thekid

Try this:
MAX(YEARFRAC(DATE(2023, 12, 31), {Possible Start}) * 12 * {Number of Employees} * {Price Equivalence}, 0)

and:
MAX(YEARFRAC(DATE(2023, 1, 1), {Possible Start}) * 12 * {Number of Employees} * {Price Equivalence}, 0)

Jim - The Monday Man
:magic_wand: Update Magic #1 – New update enhancement toolbox
:magic_wand: Column Magic :sparkles: – The magical columns toolbox
We Create Custom Apps, Integrations & Automations for monday