SOLVED

Sprint Weeks in Project

New Contributor

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)Number5Sprint Week LuTask NameStartFinish
-14478144813 Sprint 8Mon 8/08/22Fri 9/09/22
-14481644848 Sprint 9Mon 12/09/22Fri 14/10/22
sprint104485144883 Sprint 10Mon 17/10/22Fri 18/11/22
-14488644918 Sprint 11Mon 21/11/22Fri 23/12/22
#ERROR#ERROR#ERROR test  

 

Any help would be most appreciated.

 

Thanks for your help in advance.

 

 

 

 

 

 

2 Replies
best response confirmed by LouC123 (New Contributor)
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

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