Testing for Number of Weeks

  • 0
  • 1
  • Question
  • Updated 9 months 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") 
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,132 Points 3k badge 2x thumb

Posted 9 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,456 Points 50k badge 2x thumb
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.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,572 Points 20k badge 2x thumb
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)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,572 Points 20k badge 2x thumb
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)
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,132 Points 3k badge 2x thumb
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.