Discussions

Expand all | Collapse all

If statement formula problem

  • 1.  If statement formula problem

    Posted 20 days ago

    Hi QuickBase Community,

     

    I am struggling with a Formula-Duration field in which I'm trying to calculate the number of days since a customer agreement was signed using an if statement conditioned on the type of agreement signed (with its relevant date field), the customer type, and the project status.

     

    If the project is complete and it's a business customer, then I want to show the duration from agreement signed date to completed install date.

    If the project is complete and it's a residential customer, then I want to show the duration from the consent form signed date to completed install date.

     

    If the project is not complete, then I just want to show the duration between today and the date the agreement was signed.

     

    My formula results show that the formula is only returning the duration between today and the date the agreement was signed, even when the other conditions are present.

     

    Can someone please help me identify what I've got wrong with my formula?

     

    If(

    [Project Status]="Complete" and Contains([Customer Type],"Business"),

    [Completed Install Date]-[ Agreement Signed Date],

     

    [Project Status]="Complete" and Contains([Customer Type],"Residential"),

    [Completed Install Date]-[ Owner Consent Form Signed],

     

    Today()-[Agreement Signed Date])

    Thanks so much!



    ------------------------------
    Doug Crandall
    ------------------------------


  • 2.  RE: If statement formula problem

    Posted 20 days ago
    Your formula looks correct, so there must be unexpected data in the record.

    Is the Status on a test record actually "Complete" or perhaps is it "Completed".  What exactly is in the [Customer Type] field on a test record that is misbehaving.


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: If statement formula problem

    Posted 20 days ago
    Mark, thank you for verifying my formula syntax was correct. Since I'm relatively new to formulas I was convinced it was something more complex. You also identified the root cause of the problem.  I was searching for Complete when the field value is Completed! "Is there a 'd,' Pat?" I was able to solve the puzzle. :-)

    ------------------------------
    Doug Crandall
    ------------------------------



  • 4.  RE: If statement formula problem

    Posted 20 days ago
    I might have seemed to be clairvoyant to be able to see into your data, but it's really just that I have tripped over that same error too many times myself.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------