Discussions

 View Only
Expand all | Collapse all

If Statement on Rich Text - Formula

  • 1.  If Statement on Rich Text - Formula

    Posted 11-11-2020 17:12
    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 11-11-2020 17:13
    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 11-11-2020 17:17
    (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 11-11-2020 17:21
    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 11-11-2020 17:27
    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 11-11-2020 17:30
    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 11-11-2020 17:40
    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 11-11-2020 18:25
    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 11-12-2020 10:02
    Thanks Mark, I believe that worked!

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



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

    Posted 11-12-2020 10:26
    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 11-12-2020 11:01
    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
    ------------------------------