# Testing for Number of Weeks

• 0
• Question
• Updated 2 years ago
• Answered
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")
• 3,152 Points Posted 2 years ago

• 0
• 75,144 Points 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.

Ⲇanom the ultimate (Dan Diebolt), Champion

• 30,434 Points 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"  )
)```
(Edited)

Ⲇanom the ultimate (Dan Diebolt), Champion

• 30,434 Points 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..
(Edited)
• 3,152 Points 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.