# Discussions

View Only

## Testing for Number of Weeks

• #### 1.  Testing for Number of Weeks

Silver
Contributor
Posted 02-05-2018 05:00
I have a numeric field 'weeks' I have used to work out number of weeks between two dates.  Works fine. I am then using following statements in a formula text field to allocate certain text values.   but the result is not what I expect.

If(
[Project Type] = "New" and ToNumber([Weeks])>=55 and ToNumber([Weeks])<68, "Phase 1",
[Project Type] = "New" and ToNumber([Weeks])>=41 and ToNumber([Weeks])<=54, "Phase 2",
[Project Type] = "New" and ToNumber([Weeks])>=27 and ToNumber([Weeks])<=40, "Phase 3",
[Project Type] = "New" and ToNumber([Weeks])>=13 and ToNumber([Weeks])<=26, "Phase 4",
[Project Type] = "New" and ToNumber([Weeks])>=1 and ToNumber([Weeks])<=12, "Phase 5", "Phase 6")

• #### 2.  RE: Testing for Number of Weeks

Top
Contributor
Posted 02-05-2018 05:12
What�s the symptom of the problem.

Also if Weeks calculates to a number near you break point such as 54.5, your formula is not catching that.

• #### 3.  RE: Testing for Number of Weeks

Top
Contributor
Posted 02-05-2018 05:20
I missed Mark's point. Maybe update as follows:
`var Number NumWeeks = ToNumber([Weeks]);If([Project Type] = "New",  If(    55 <= \$NumWeeks and \$NumWeeks < 68, "Phase 1",    41 <= \$NumWeeks and \$NumWeeks < 55, "Phase 2",    27 <= \$NumWeeks and \$NumWeeks < 41, "Phase 3",    13 <= \$NumWeeks and \$NumWeeks < 27, "Phase 4",     1 <= \$NumWeeks and \$NumWeeks < 13, "Phase 5",    "Phase 6"  ) )`

• #### 4.  RE: Testing for Number of Weeks

Top
Contributor
Posted 02-05-2018 05:18
I would write it like this:
`var Number NumWeeks = ToNumber([Weeks]);If([Project Type] = "New",  If(    55 <= \$NumWeeks and \$NumWeeks < 68, "Phase 1",    41 <= \$NumWeeks and \$NumWeeks < 54, "Phase 2",    27 <= \$NumWeeks and \$NumWeeks < 40, "Phase 3",    13 <= \$NumWeeks and \$NumWeeks < 26, "Phase 4",     1 <= \$NumWeeks and \$NumWeeks < 12, "Phase 5",    "Phase 6"  ))`

Writing this formula in this fashion will allow you to scan and update the breakpoints easily as everything is in a natural order and "pointing" in the same direction.

But (1) you did not state what you expect and (2) you said [Weeks] was a Numeric field so I don't know why you were converting it to a number with ToNumber().

Also, I would consider what you want when \$NumWeeks was 68 or greater as your draft formula is going to default to "Phase 6" and I am not sure that is what you want..

• #### 5.  RE: Testing for Number of Weeks

Silver
Contributor
Posted 02-05-2018 21:22
Thank you so much.  It works great.  I was expecting a categorisation based on the weeks test.  I created the week numbers as numeric field but to convert to these to weeks, I ToWeeks" in the week formula.  May be I should remove the ToNumber from the formula.