Forum Discussion

Copper Contributor

Oct 11, 2022

Solved

Sprint Weeks in Project

Hi - My challenge is to have the Project Sprints updated if the task start date moves. I have added Sprint weeks (to and from) in the Sprint Table. However it doesnt seem to update/change dynamically...

PlannersPlace
Oct 12, 2022
LouC123

Try this formula

IIf([Number1]>"44850" And [Number1]<"44886","sprint10", IIf([Number1]>"44885" And [Number1]<"44920","sprint11","unknown"))

This is a nested IIF
The first blue part is your test criteria for sprint 10 and if it is true, it returns the first green part
But if it is false, we then test for sprint 11, which is the second blue part and if it is true, it returns the second green part
If test for sprint 11 is also false, then we return unknown, which is the red part.
=jerome

PlannersPlace

Brass Contributor

Oct 12, 2022

LouC123

Try this formula

IIf([Number1]>"44850" And [Number1]<"44886","sprint10", IIf([Number1]>"44885" And [Number1]<"44920","sprint11","unknown"))

This is a nested IIF

The first blue part is your test criteria for sprint 10 and if it is true, it returns the first green part
But if it is false, we then test for sprint 11, which is the second blue part and if it is true, it returns the second green part
If test for sprint 11 is also false, then we return unknown, which is the red part.

=jerome

LouC123
Copper Contributor
Oct 12, 2022
PlannersPlace

Well, what can I say...thanks - works treat 🙂

I had to add the rest of the sprints:

IIf([Number1]>"44850" And [Number1]<"44884","sprint10",
IIf([Number1]>"44885" And [Number1]<"44919","sprint11",
IIf([Number1]>"44920" And [Number1]<"44933","Xmas",
IIf([Number1]>"44934" And [Number1]<"44968","sprint12",
IIf([Number1]>"44969" And [Number1]<"45003","sprint13",
IIf([Number1]>"45004" And [Number1]<"45038","sprint14",
IIf([Number1]>"45039" And [Number1]<"45066","sit1",
IIf([Number1]>"45067" And [Number1]<"45094","sit2",
IIf([Number1]>"45095" And [Number1]<"45116","UAT1",
IIf([Number1]>"45116" And [Number1]<"45129","UAT2",
IIf([Number1]>"451310" And [Number1]<"45157","Go-live","unknown")))))))))))

Thanks again - dynamically updates too !!