Forum Discussion

CassidySzul's avatar
CassidySzul
Qrew Member
6 years ago

Date Formula - IF Statement Error


I have a date- formula field with the below working if statement:

If(IsNull([PO Issue Date]) = false, WeekdayAdd([PO Issue Date], [Lead Time +Transit Time (Weeks)]*7), WeekdayAdd([Plan Issue PO Date], [Lead Time +Transit Time (Weeks)]*7)))


I want to add in an initial if statement for If [Lead Time +Transit Time (Weeks)] is null, then show either 0, 1/1/1900, or " " in the field but keep getting an error.
Formulas and functions
  • AustinK's avatar
    AustinK
    Qrew Commander
    6 years ago
    There were some missing parenthesis and various other things. You probably don't even need all the ones I left in there.

    You can try this but it's obviously not tested as I don't have those fields.

    If(
    IsNull([PO Issue Date]), WeekdayAdd([PO Issue Date], 
    ([Lead Time +Transit Time (Weeks)]*7), WeekdayAdd([Plan Issue PO Date], 
    isNull([Lead Time +Transit Time (Weeks)]), todate("jan 1, 1900"),
    ([Lead Time +Transit Time (Weeks)]*7))

    • AustinK's avatar
      AustinK
      Qrew Commander
      6 years ago
      I can no longer edit this... 


      Actually, looking at this formula it looks to be pretty spaghettied to start with so what I did will not work. Can you post a simpler version before you made changes that worked, and then say what you would like it to do?

  • LauraThacker's avatar
    LauraThacker
    Qrew Captain
    6 years ago
    You had an error in the first part of your IF statement.  This should give you the output you are looking for.

    If(IsNull([Lead Time +Transit Time (Weeks)]),null,
    If(IsNull([PO Issue Date]), WeekdayAdd([PO Issue Date], [Lead Time +Transit Time (Weeks)]*7), WeekdayAdd([Plan Issue PO Date], [Lead Time +Transit Time (Weeks)]*7)))
    • LauraThacker's avatar
      LauraThacker
      Qrew Captain
      6 years ago
      Give some thought as to whether you need to multiply the # of weeks by 7 or by 5 to get the correct WeekdayAdd result.
    • CassidySzul's avatar
      CassidySzul
      Qrew Member
      6 years ago
      When i add If(IsNull([Lead Time +Transit Time (Weeks)]),null, to the beginning of my If Statement, it is now only populating the field based on this part (If(IsNull([PO Issue Date]), WeekdayAdd([PO Issue Date], [Lead Time +Transit Time (Weeks)]*7), ) of the if statement and isn't for the 2nd part.

      I believe it is missing something after , null, but i do not know what to add without getting an error
    • AustinK's avatar
      AustinK
      Qrew Commander
      6 years ago
      Having multiple if statements in there makes it confusing. You only need one. QuickBase will take the first one that is true and discard the rest. So you have to build the formula with that in mind sometimes. If you ever have a scenario where 2 things could calculate as true, you might not get the desired result.

      I can try and illustrate that with an example.

      If(
      [My Text] = "bleh", "not interested",
      [My Other Text] = "no way", "hung up on me!",
      [My Last Text] = "totally", "ready to buy",
      "no status"
      )

      Does that help at all? You do not need the extra IF, or should not in most cases. Do that and double check line by line that it is doing what you expect it to.

      Also the formula posted above seems to have an extra parenthesis at the end if I'm not mistaken. Remove that.