Multiple IF/AND Statements to populate "Sprint X" based on the start date of the timeline

[maresaj98 (Alan Mares)]

Hi Everyone,

I am trying to create an “automation” using an IF/AND formula based on a date range and the start date of our timeline. I am hoping to “auto” populate Sprint numbers to categorize tasks, deliverables, milestones.

I tested the below formula in excel (replacing dates from excel with Timeline Start Date) and it worked perfectly but when i tried to move the formula to monday.com it gave me an illegal formula error message. Does anyone have any insights on how to fix or solution for my problem?

Thanks in advance!

=IF(AND({Timeline#Start}>=DATE(2023,9,18),{Timeline#Start}<=DATE(2023,10,8)),“Sprint 9”,IF(AND({Timeline#Start}>=DATE(2023,10,9),{Timeline#Start}<=DATE(2023,10,29)),“Sprint 10”,IF(AND({Timeline#Start}>=DATE(2023,10,30),{Timeline#Start}<=DATE(2023,11,19)),“Sprint 11”,IF(AND({Timeline#Start}>=DATE(2023,11,20),{Timeline#Start}<=DATE(2023,12,10)),“Sprint 12”,IF(AND({Timeline#Start}>=DATE(2023,12,11),{Timeline#Start}<=DATE(2023,12,31)),“Sprint 13”,IF(AND({Timeline#Start}>=DATE(2024,1,1)),“Sprint 14”))))))

[fifield21 (Carrie)]

Idk what you are trying to accomplish but attempted a small test try this and see if it works:

IF(
AND(
{Timeline#Start} >= DATE(2023, 9, 18),
{Timeline#Start} <= DATE(2023, 10, 8)
),
“Sprint 9”,
IF(
AND(
{Timeline#Start} >= DATE(2023, 10, 9),
{Timeline#Start} <= DATE(2023, 10, 29)
),
“Sprint 10”,
IF(
AND(
{Timeline#Start} >= DATE(2023, 10, 30),
{Timeline#Start} <= DATE(2023, 11, 19)
),
“Sprint 11”,
IF(
AND(
{Timeline#Start} >= DATE(2023, 11, 20),
{Timeline#Start} <= DATE(2023, 12, 10)
),
“Sprint 12”,
IF(
AND(
{Timeline#Start} >= DATE(2023, 12, 11),
{Timeline#Start} <= DATE(2023, 12, 31)
),

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

Hi Alan,

Try to start from the furthest date and go back to make it a lot simpler and get rid of the AND. You also don’t need to use the DATE function.

I am not going to rewrite your whole formula, but try something like this:

IF({Timeline#Start>="2024-01-01", "Sprint 14",
   IF({Timeline#Start>="2023-12-11", "Sprint 13",
      IF({Timeline#Start>="2023-11-20", "Sprint 12")
   )
)

[maresaj98 (Alan Mares)]

@GCavin

IF({Timeline#Start>="2024-01-01", "Sprint 14",
   IF({Timeline#Start>="2023-12-11", "Sprint 13",
      IF({Timeline#Start>="2023-11-20", "Sprint 12")

Hi Carrie,

I am trying to get the formula to populate what Sprint a task will fall in based on the state date of the timeline. The timeline will not always fall on the start of a Sprint start date which is why i cannot automate it and am trying to find a formula to solve for it.

I tested out your formula but i still get the same ‘illegal formula’ message.

IF(AND({Timeline#Start}>=DATE(2023, 9, 18),{Timeline#Start}<=DATE(2023, 10, 8)
),“Sprint 9”,IF(AND({Timeline#Start}>=DATE(2023, 10, 9),{Timeline#Start}<=DATE(2023, 10, 29)
),“Sprint 10”,IF(AND({Timeline#Start}>=DATE(2023, 10, 30),{Timeline#Start}<=DATE(2023, 11, 19)
),“Sprint 11”,IF(AND({Timeline#Start}>=DATE(2023, 11, 20),{Timeline#Start}<=DATE(2023, 12, 10)
),“Sprint 12”,IF(AND({Timeline#Start}>=DATE(2023, 12, 11),{Timeline#Start}<=DATE(2023, 12, 31)
),“Sprint 13”,IF(AND({Timeline#Start}>=DATE(2024, 1, 1),"Sprint 14))))))))))))

[maresaj98 (Alan Mares)]

Hi Cavin,

I appreciate your response. I am unsure if your formula will solve if the date falls within two dates but i did test it and i get an ‘illegal formula’ message.

IF({Timeline#Start}>=“2024-01-01”, “Sprint 14”,IF({Timeline#Start}>=“2023-12-11”, “Sprint 13”,IF({Timeline#Start}>=“2023-11-20”, “Sprint 12”),IF({Timeline#Start}>=“2023-10-30”, “Sprint 11”,IF({Timeline#Start}>=“2023-10-09”, “Sprint 10”,IF({Timeline#Start}>=“2023-09-18”, “Sprint 9”))))))

[fifield21 (Carrie)]

hmmm… let me play around with this some more. Wondering if we can do this in make.com

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

Hi Alan,

I think you missed the point of my response. You’re overcomplicating your formula.
Since your sprints follow one another, you just need to test against one date but need to go backward and not forward.

Look at what I wrote:

IF({Timeline#Start>=“2024-01-01”, “Sprint 14”,
IF({Timeline#Start>=“2023-12-11”, “Sprint 13”,
IF({Timeline#Start>=“2023-11-20”, “Sprint 12”)

If date is after january 1st. Sprint 14. STOPPED.
Then if date is after Dec 11, Sprint 13 (because we already know it is not after Jan 1st if you got here)
Then if date is after Nov 20, Sprint 12 (again because we already know it is not after Dec 11).

---

As a side note, if you want to make it a lot easier to write your formulas, take a look at the Advanced Formula Booster app. I am the main developer and we just launched it this monday. If you are interested, I can provide with a discount because we are in the launch period.*

In the app, you would create a {Sprint} text column (we don’t use the Formula column) and one of the ways of doing it would be:

Clean and simple.

[maresaj98 (Alan Mares)]

Hi Cavin,

I did previously test out your formula and received the ‘illegal formula’ message.

Unfortunately, my company does not allow us to download at apps so the ‘Advance Formula Booster’ would not be an option. I appreciate your assistance.

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

If it is the formula you included in your one before last message, it is not by any mean what I suggested.
If you tried my formula and it generates an error, then include your code here, so that we can troubleshoot it.

[maresaj98 (Alan Mares)]

I attempted two solutions. One from Carrie and one from you. The two different formulas resulted in the same ‘illegal formula’ message. Below is a screenshot of the formula that i used which was based on what you originally suggested. As far as i can tell it is exactly what you suggested.

Here is what it looks like in Monday.

Below is the formula that you suggested which matches my formula (aside from the missing brackets for the column field)

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

The correct syntax is:

IF({Timeline#Start}>="2024-01-01","Sprint 14",IF({Timeline#Start}>="2023-12-11","Sprint 13",IF({Timeline#Start}>="2023-11-20","Sprint 12",IF({Timeline#Start}>="2023-10-30","Sprint 11",IF({Timeline#Start}>="2023-10-09","Sprint 10", IF({Timeline#Start}>="2023-09-18","Sprint 9",""))))))

I used > and not >= in my screenshots, but it doesn’t make a difference.

Look at your formula. You have a ) after Sprint 12 that shouldn’t be there.

[maresaj98 (Alan Mares)]

It works now. Thank you!