Discussions

 View Only
  • 1.  Date Formula - IF Statement Error

    Posted 07-22-2019 18:18

    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.


  • 2.  RE: Date Formula - IF Statement Error

    Posted 07-22-2019 18:24
    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))



  • 3.  RE: Date Formula - IF Statement Error

    Posted 07-22-2019 18:27
    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?



  • 4.  RE: Date Formula - IF Statement Error

    Posted 07-22-2019 18:27
    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)))


  • 5.  RE: Date Formula - IF Statement Error

    Posted 07-22-2019 18:41
    Give some thought as to whether you need to multiply the # of weeks by 7 or by 5 to get the correct WeekdayAdd result.


  • 6.  RE: Date Formula - IF Statement Error

    Posted 07-22-2019 19:06
    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


  • 7.  RE: Date Formula - IF Statement Error

    Posted 07-22-2019 19:28
    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.


  • 8.  RE: Date Formula - IF Statement Error

    Posted 07-22-2019 19:35
    Cassidy,
    I think you're using the wrong field in your 2nd line.


    If(IsNull([Lead Time +Transit Time (Weeks)]),null,

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

    WeekdayAdd([PO Issue Date], [Lead Time +Transit Time (Weeks)]*7)))


    The 2nd line was saying "if it is empty" then use that same date (empty) and add Weeks * 7 days to it - which would be wrong. 

    OR you could have written it as:

    If(IsNull([Lead Time +Transit Time (Weeks)]),null,

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

    WeekdayAdd([Plan Issue Date], [Lead Time +Transit Time (Weeks)]*7)))





  • 9.  RE: Date Formula - IF Statement Error

    Posted 07-22-2019 19:41
    The formulas as posted before did not have an ending single False statement and had an extra parenthesis on the end(because of that double if it didn't need, I think). It is very spaghetti so hard to read with all the parenthesis.

     I'm also not positive weekdayadd can be used like it is? But it might work, just never tried that lol. It really depends on what those fields are exactly. Sometimes you have to be more explicit and actually turn them into dates or numbers.

    WeekdayAdd([PO Issue Date], [Lead Time +Transit Time (Weeks)]*7)