I am trying figure out a date formula to calculate overdue payments

[Taylor13 (Taylor Foley)]

I am trying to set up a column to calculate overdue payments. I have a payment amount column, then a couple of columns for the accounts that the payment would go to. From there I have an “outstanding” column that populates with the payment amount minus any deposits made to one of the bank accounts. I am hoping I can have the overdue column populate with the payment amount if the payment due date has passed. I get easily confused with the monday versions of formulas. I was playing with IF({Date}<TODAY(),{Outstanding}," ") but had no luck. Thanks in advance for any help provided.

See photo

[TimB (Tim Beldman)]

Hey Taylor,

Seems like the TODAY() is not in the same format as your desired Date column.

To get around this:
Insert a new formula column, name this column “Today”

TODAY()

Now when you have your Today column simply add this to your Overdue formula column:

IF(FORMAT_DATE({Date}, "YYYY-MM-DD") < FORMAT_DATE({Today}, "YYYY-MM-DD"), "$" & TEXT({Outstanding}, "$#,##0.00"), "$0.00")

Hope this helps!

Tim

[GCavin (Gilles Cavin - Reinventing Formulas in monday.com)]

Hi Taylor,

Just make sure your dates are formatted the same way.

FORMAT_DATE(TODAY(),"YYYY-MM-DD") returns 2023-12-05

So if you use this formula, it should work

IF(FORMAT_DATE(TODAY(),"YYYY-MM-DD")<FORMAT_DATE(TODAY(),"YYYY-MM-DD"),{Outstanding},"")

Note that the hyphens are not necessary since you’re not displaying any data. “YYYYMMDD” would work as well.

---

If you’re creating a number of formulas, you may want to look at the Advanced Formula Booster. It breaks free of the formula column and can update any column.

It is a lot simpler to use.

In this particular case, if you use TODAY() in the AFB app, it returns 2023-12-05 without the need to use a format function and the comparison is as simple as the one you intuitively tried.

There are many other advantages.

[Taylor13 (Taylor Foley)]

That formula worked great. Thanks! It for some reason though is not adding up the sum of the overdue payments in the column summary. Any idea why this is? How can I fix? Is it because of the TEXT({Outstanding}?

[Taylor13 (Taylor Foley)]

That formula worked great. Thanks! It for some reason though is not adding up the sum of the overdue payments in the column summary. Any idea why this is? How can I fix? Is it because of the TEXT ({Outstanding}?

[Taylor13 (Taylor Foley)]

Hey Tim, That formula worked great. Thanks! It for some reason though is not adding up the sum of the overdue payments in the column summary. Any idea why this is? How can I fix? Is it because of the TEXT ({Outstanding}?

Thanks!

[TimB (Tim Beldman)]

Hey Taylor,

Yes your right its because of the TEXT try this code instead:

IF(FORMAT_DATE({Date}, "YYYY-MM-DD") < FORMAT_DATE({Today}, "YYYY-MM-DD"), {Outstanding}, 0)

Hope this helps,

Tim

[Taylor13 (Taylor Foley)]

That worked. Thank you!

[Taylor13 (Taylor Foley)]

Hey Tim,
Would you happen to be able to also help me with this formula? I would like to calculate the number of years since a date. If I started in the industry 01/02/2012 I would like it to compare to today and figure how many years of experience I have.

[TimB (Tim Beldman)]

Hey Taylor,

Of course I used a different approach:

I have a Date column {Start in Industry}

And i have created 2 Formula columns

1. First one counts the amount of days untill now. I called it {Amount of days}

IF(
  YEAR({Start in Industry}) > 0,
  DAYS(TODAY(), {Start in Industry}, 1),
  0
)

2. Second Formula column I created I named {Years of Exp.}

IF(
  YEAR({Start in Industry}) > 0,
  INT(DAYS(TODAY(), {Start in Industry}) / 365),
  0
)

Screenshot:

Let me know if you have any questions,

Tim

[Taylor13 (Taylor Foley)]

@TimB

IF(
  YEAR({Start in Industry}) > 0,
  INT(DAYS(TODAY(), {Start in Industry}) / 365),
  0
)

Thank you very much!