Discussions

Expand all | Collapse all

If Statement on Rich Text - Formula

  • 1.  If Statement on Rich Text - Formula

    Posted 15 days ago
    Hey Everyone,

    I'm running into an issue, I'm attempting to total up some durations that are being calculated across multiple fields (7), that way I can figure out overall duration for a job. I keep getting errors trying to use an if statement. I'm trying to use an if statement to say if there is no value, then to display 0. 

    Thanks,

    ------------------------------
    Alex Bennett
    ------------------------------


  • 2.  RE: If Statement on Rich Text - Formula

    Posted 15 days ago
    Can you post your latest attempt?

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



  • 3.  RE: If Statement on Rich Text - Formula

    Posted 15 days ago
    (ToText(Ceil([6. Sub Duration]/8) & " " & "Days"))

    So this is the code to take the final number and display it in days. If I try to add an If statement to that to say if that result is blank, then display 0 I get errors

    ------------------------------
    Alex Bennett
    ------------------------------



  • 4.  RE: If Statement on Rich Text - Formula

    Posted 15 days ago
    What does this formula now display when [6. Sub Duration] is blank?   I suppose that it just says "Days".

    try this

    var text Result = (ToText(Ceil([6. Sub Duration]/8) & " " & "Days"));

    IF(Trim($Result)="", "0 Days", $Result)


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



  • 5.  RE: If Statement on Rich Text - Formula

    Posted 15 days ago
    So some sections just say "Days" and others are left blank with nothing. Even If I add that code.

    ------------------------------
    Alex Bennett
    ------------------------------



  • 6.  RE: If Statement on Rich Text - Formula

    Posted 15 days ago
    Can you try this version

    var text Result =
    ToText(Nz(Ceil([6. Sub Duration]) / 8; 

    IF(Trim($Result)="", "0", $Result) &  " " & "Days"));

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



  • 7.  RE: If Statement on Rich Text - Formula

    Posted 15 days ago
    Getting errors with the first ';' says expecting ',' when I change, I get error with bottom if statement.

    ------------------------------
    Alex Bennett
    ------------------------------



  • 8.  RE: If Statement on Rich Text - Formula

    Posted 15 days ago
    try this.  my brackets were pretty messed up.

    var text Result = 
    ToText(Nz(Ceil([6. Sub Duration])) / 8); 

    IF(Trim($Result)="", "0", $Result) &  " " & "Days";

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



  • 9.  RE: If Statement on Rich Text - Formula

    Posted 15 days ago
    Thanks Mark, I believe that worked!

    ------------------------------
    Alex Bennett
    ------------------------------



  • 10.  RE: If Statement on Rich Text - Formula

    Posted 15 days ago
    Can I ask some advice? That worked so blank data will show up as 0 Days. But now when I'm trying to write a formula to calculate all these totals together to show overall days, I still have to skip that field to get a number of days. If I include it, it just says Days on my total. If I remove it and add all others, it gives me a wrong total (13 Days), which should read 17 Days. 

    This is the code for each of those formulas i'm trying to combine:

    (ToText(Ceil([1. Sub Duration]/10) & " " & "Days"))
    //7 total fields, each 1., 2., 3., etc. This is Rich Text - Formula 

    This is my Formula to combine all for overall days:

    ToText(Ceil([1. Sub Duration]/10+[2. Sub Duration]/8+[3. Sub Duration]/8+[4. Sub Duration]/8+[5. Sub Duration]/8+[7. Sub Duration]/8) & " " & "Days")

    //Notice how I have to skip [6. Sub Duration] because it has no value, and when added to the calculation, I get nothing
    //Each of those fields takes my final calculations in hours, which I'm dividing by 10 for Plumbing since they work 4/10s, and 8 by all others for 8 hour days. This Formula gives me 13 Days, which should be 17.

    The trick is that when a job comes over, they can have any variation of Departments associated with the job (Plumbing, HVAC, Engineering, etc) But if they aren't involved in the job, there is no estimated budget, therefore no calculation to see how long that department would take to complete each phase (Sub, Rough, Finish, Close) All calculations are complete, besides me trying to add up all of those 4 phases totals so I have duration in Days for those 4 phases per job. 




    ------------------------------
    Alex Bennett
    ------------------------------



  • 11.  RE: If Statement on Rich Text - Formula

    Posted 15 days ago
    Never mind Mark, got a solution. I was overcomplicating it and the If Statement needed to be further down the chain to effect other formulas... oops!!! 

    Thanks for all your help!!

    ------------------------------
    Alex Bennett
    ------------------------------