Calculating Dates with Formulas not working as expected

Platform discussions Special workflows & use cases
,
1

Hey!

I’ve been playing with the formula field in Monday.com and I’m struggling to understand the logic of why it isn’t working as expected.

Here’s how my board is setup:

Table Example
Name Status Campaign Timeline Expected Delivery Date

I am trying to do a calculation on the timeline start date so if we have less than 3 days before the campaign needs to launch, it will just show the expected delivery date. Otherwise, if it is a month into the future for example, it will show 3 days before that date.

I started with this formula which shows the date -3 days:

Formula

FORMAT_DATE(SUBTRACT_DAYS({Campaign Timeline#Start}, 3),“YYYY-MM-DD”)

I then created this formula:

Formula

IF(FORMAT_DATE({Campaign Timeline#Start},“YYYY-MM-DD”) <= FORMAT_DATE(ADD_DAYS(NOW(), 3),“YYYY-MM-DD”), {Campaign Timeline#Start}, FORMAT_DATE(SUBTRACT_DAYS({Campaign Timeline#Start}, 3),“YYYY-MM-DD”))

The issue is that this doesn’t appear to work correctly. I thought, best to simplify it and tried this formula:

Formula

IF(FORMAT_DATE({Engagement Timeline#Start},“YYYY-MM-DD”) < FORMAT_DATE(ADD_DAYS(TODAY(), 3),“YYYY-MM-DD”), “LESS THAN TODAY”, “MORE THAN TODAY”)

The issue is that it doesn’t work as expected. Instead it shows the following:

Output

Todays Date
March 11th, 2024

Name Status Campaign Timeline Expected Delivery Date
Advert on subway Planned March 1st 2024 - March 28th 2024 MORE THAN TODAY

Am I going crazy? The formula AI agrees with what I created so I’m at a loss.

PS - Next step will to take the status field into account, but one thing at a time!

2

Hi Aaron,

I just tested your formula and I get the correct result:

2024-03-11_20-42-26

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, the same board or up to 2 other boards!
  • In one formula, update multiple columns from multiple items.

Check our blog for real use cases.