Need formula help please

Platform discussions
1

Hi, I’ve created a formula where the total billed column is calculated via the monthly billed and the start and end dates. However, I would like the total billed to be the same as the monthly billed if the end date is left blank. I cannot seem to get the AI prompt to work no matter how I reword it. Any suggestions?

This is my current formula:

IF({End Date} = “”, {Monthly Billed}, TEXT(ROUNDUP((YEAR({End Date}) - YEAR({Start Date})) * 12 + MONTH({End Date}) - MONTH({Start Date}) + IF(DAY({End Date}) > DAY({Start Date}), 1, 0), 0) * {Monthly Billed}, “$#,##0.00”))

And a screenshot of the current board where the formula is used:

image
image478×612 30.2 KB

2

Hi @Mysticat,

The issue is that monday.com throws an “invalid parameter” error when YEAR(), MONTH(), or DAY() are used on blank Date fields. You need to handle blank dates inside the date functions themselves.

This version works:

IF( OR({End Date} = “”, {Start Date} = “”), {Monthly Billed}, ROUNDUP( ( (YEAR(IF({End Date} = “”, DATE(2000, 1, 1), {End Date})) - YEAR(IF({Start Date} = “”, DATE(2000, 1, 1), {Start Date})) ) * 12 + (MONTH(IF({End Date} = “”, DATE(2000, 1, 1), {End Date})) - MONTH(IF({Start Date} = “”, DATE(2000, 1, 1), {Start Date})) ) + IF( DAY(IF({End Date} = “”, DATE(2000, 1, 1), {End Date})) > DAY(IF({Start Date} = “”, DATE(2000, 1, 1), {Start Date})), 1, 0 ) ) * {Monthly Billed}, 0 ) )

image
image674×241 12.2 KB

3

Hello @Mysticat
You’re super close

Right now your IF check is looking for an empty string, but Date columns in monday don’t behave like text. The safest approach is to explicitly check for “blank” using the date value itself.

Try wrapping your current formula with a blank End Date check like this:

IF(ISBLANK({End Date}), {Monthly Billed}, <your existing calculation>)

So your structure becomes:
If End Date is blank then just return Monthly Billed
Otherwise run the month difference math

This is the same pattern monday recommends for formulas that need to handle empty fields.

If you’d like hands-on help or want us to walk through this live, you can book a 1:1 paid 60-minute strategy session with our team here:
:backhand_index_pointing_right: Calendly

4

At first I plugged it in and clicked “set formula”, and it would not work. Then I asked the AI to analyze the formula. I then clicked “Insert” with what it gave me, and it worked! Is there any difference?

IF(OR({End Date} = “”, {Start Date} = “”), {Monthly Billed}, ROUNDUP(((YEAR(IF({End Date} = “”, DATE(2000, 1, 1), {End Date})) - YEAR(IF({Start Date} = “”, DATE(2000, 1, 1), {Start Date}))) * 12 + (MONTH(IF({End Date} = “”, DATE(2000, 1, 1), {End Date})) - MONTH(IF({Start Date} = “”, DATE(2000, 1, 1), {Start Date}))) + IF(DAY(IF({End Date} = “”, DATE(2000, 1, 1), {End Date})) > DAY(IF({Start Date} = “”, DATE(2000, 1, 1), {Start Date})), 1, 0)) * {Monthly Billed}, 0))

1 Like
5

Hi Misty! My name is Alea, and I am on the Customer Experience team at monday.com. It looks like you ran into a classic case of hidden formatting issues.

The logic in both versions of your formula is actually the same. The reason the second one worked likely comes down to formatting. In your first version, you used “curly” or “smart” quotes (“ ”). The monday.com formula builder reads those as illegal characters, which caused the error. The working version used straight quotes (" "), which the system recognizes properly.

When you clicked ‘Insert’ from the AI assistant, it automatically cleaned up the syntax behind the scenes. :raising_hands: That includes converting smart quotes to straight quotes, making sure parentheses match, and fixing spacing. So even though the logic didn’t change, the formatting did.

It’s a subtle difference, but one that can definitely trip things up. Please let us know if you have any additional questions on this - we’re more than happy to help! :slightly_smiling_face:

6

Guessing there might few extra spaces when I pasted it. Glad it worked!!