Sprint Weeks in Project

[LouC123]

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. (I am not sure if it is supposed to)

If the Sprint field cannot be updated when a date changes, then I thought I might be able to create new field eg Text 25 and have a formula based on the date using the IIF function. 

My requirement is compare the Sprint Dates and Task Start date and put in the relevant Sprint Number

Initially I tried a formula with the Start Date fields but seemed to get errors so I tried adding additional fields and done a Date Val to get the Date as a numeric and set the IFF against the numeric value that sort of worked. 

However because there are 8 sprints based on a 5 week period the formula has to cover all variables/sprints.

I tried a test with this:

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

I was getting the following entries:

sprint10 - which is good

error - which is also good as it means there is no data in the two fields

 -1  which I'm not sure what's causing this

The Sprints are

SP Test 1 (Text25)	(Number1)	Number5	Sprint Week Lu	Task Name	Start	Finish
-1	44781	44813		Sprint 8	Mon 8/08/22	Fri 9/09/22
-1	44816	44848		Sprint 9	Mon 12/09/22	Fri 14/10/22
sprint10	44851	44883		Sprint 10	Mon 17/10/22	Fri 18/11/22
-1	44886	44918		Sprint 11	Mon 21/11/22	Fri 23/12/22
#ERROR	#ERROR	#ERROR		test

Any help would be most appreciated.

Thanks for your help in advance.

[#2 · Accepted solution]

@LouC123 

Try this formula

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

This is a nested IIF

1. The first blue part is your test criteria for sprint 10 and if it is true, it returns the first green part
2. 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
3. If test for sprint 11 is also false, then we return unknown, which is the red part.

=jerome

[#3]

@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 !!