Forum Discussion
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. (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.
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
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
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 !!
