Skip to main content
Question

Testing for Number of Weeks

  • February 5, 2018
  • 0 replies
  • 48 views
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") 
      This topic has been closed for replies.

      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.
      • anomDiebolt
      • Quickbase Alumni
      • February 5, 2018
      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..
      • ArshadKhwaja
      • Author
      • Quickbase Alumni
      • February 6, 2018
      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.  
      Terms & ConditionsAccessibility statement