Discussions

Expand all | Collapse all

Finish Date Formula

  • 1.  Finish Date Formula

    Posted 19 days ago
    Hey everyone,

    I'm attempting another formula without success. I have a start date, and a Rich Text - Formula field that calculates my final numbers to display a total in Days:

    (ToText(Round(([3. Close Duration]+[3. Finish Duration]+[3. Rough Duration]+[3. Sub Duration])/8, 0.01) & " " & "Days"))

    My question, is how can I get that number, being used with the Start Date to calculate the Finish date? I keep getting errors since it's 'text' and can't convert to a date. 

    Any help would be appreciated!

    Thanks,

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


  • 2.  RE: Finish Date Formula

    Posted 18 days ago
    I think you just have misplaced parenthesis.
    Try this:

    ToText(Round(([3. Close Duration]+[3. Finish Duration]+[3. Rough Duration]+[3. Sub Duration])/8, 0.01))& " " & "Days"

    ------------------------------
    Ahuva Brown
    ------------------------------



  • 3.  RE: Finish Date Formula

    Posted 18 days ago
    Thanks again!

    Also, I'm not quite sure how to add an if is null function to this so that if there is no data, Days won't display. Right now every field displays 'Days' even if there is no calculation in that field. Can I use 'IsNull' to say if there is no data, then leave the field blank? Sorry still fairly new to these formulas within quickbase!

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



  • 4.  RE: Finish Date Formula

    Posted 18 days ago
    In this case, I'd use a formula variable. I chose to name the variable numOfDays, but you can use whatever name you want.

    var text numOfDays = ToText(Round(([3. Close Duration]+[3. Finish Duration]+[3. Rough Duration]+[3. Sub Duration])/8, 0.01))& " " & "Days";

    If($numOfDays != "0", $numOfDays & " " & "Days", " ")

    If that doesn't work, try it this way. Depending on how you set up your fields, an empty result will either show up as 0 or " ":

    If($numOfDays != " ", $numOfDays & " " & "Days", " ")

    ------------------------------
    Ahuva Brown
    ------------------------------



  • 5.  RE: Finish Date Formula

    Posted 18 days ago
    I also wanted to see if you had any opinions on how to formulas are read, so right now I don't know if they are accurately displaying based on workdays (8 hour/ 10 hour). By me dividing that by /8 or /10 accurately accomplishing that goal?

    Thanks,

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



  • 6.  RE: Finish Date Formula

    Posted 18 days ago
    Can you provide sample data for the duration fields?

    ------------------------------
    Ahuva Brown
    ------------------------------



  • 7.  RE: Finish Date Formula

    Posted 18 days ago
    (ToText(Round([1. Sub Duration]/10, 0.01) & " " & "Days"))
    So far this is my only code that attempts to 'divide' to get result based on workdays (10hr / 8hr)

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



  • 8.  RE: Finish Date Formula

    Posted 18 days ago
    Duration fields show up as ex: 7.52 Days. if it's under a day, it displays as 0.87 Days.

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



  • 9.  RE: Finish Date Formula

    Posted 18 days ago
    I may have misunderstood, but my understanding is that [1. Sub Duration] (and the other duration fields) is the number of days a project would take to complete, and you're trying to calculate how many workdays it would take to complete?

    So if [1. Sub Duration] = 7.52 days, does that mean 7 24 hour days? In which case you'd want to first calculate the number of hours there are in 7 days, and then divide that result by 8 (or 10, if your workday is 10 hours) to determine the number of workdays it would take to complete.

    So that would be 7.52x24 = 180.48
    Then then 180.48/8 = 22.56


    ------------------------------
    Ahuva Brown
    ------------------------------



  • 10.  RE: Finish Date Formula

    Posted 18 days ago
    I think my final total is in hours. 

    So here's my logic, I have some fields that I'm combining to get totals.
    -> An estimated Dollar amount of budget = $4,147.74
    -> An Avg Hourly Rate for that department = $27.56
    -> An estimated # of employees assigned = 2

    My final result being roughly 8 Days. Because i'm attempting to divide by 10 to figure these employees are working 4/10's. I'm just not sure if that math is correct to get 8 Days, assuming they are only working 10 hours per day. ​

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



  • 11.  RE: Finish Date Formula

    Posted 18 days ago
    Now if I change my code to round only to the nearest Day so instead of 7.52, it rounds and displays as 8 Days. How can I go about displaying in hours if the total comes out to be 0.87 Days?

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



  • 12.  RE: Finish Date Formula

    Posted 18 days ago
    Or better yet, maybe an If statement to say if it's under an 1 day, then instead of showing hours, i'll just display it as 1 Day.

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



  • 13.  RE: Finish Date Formula

    Posted 18 days ago
    This will round up to 8: Round(7.52, 1)
    This will round up to 1: Round(0.87, 1)

    One you get your total number of days, you can divide that result by 10 to get the number of 10 hour workdays.

    ------------------------------
    Ahuva Brown
    ------------------------------



  • 14.  RE: Finish Date Formula

    Posted 18 days ago
    Actually, first you'd need to calculate the amount of hours there are in the total number of days, and then divide that result by 10

    ------------------------------
    Ahuva Brown
    ------------------------------



  • 15.  RE: Finish Date Formula

    Posted 18 days ago
    Ok I think I have the correct results now! Is there something I can add to my formula to say if the total is under a day, then just display 1 day? Maybe an if statement if under 8 or 10?

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



  • 16.  RE: Finish Date Formula

    Posted 18 days ago
    Can you show me what you have so far?

    ------------------------------
    Ahuva Brown
    ------------------------------



  • 17.  RE: Finish Date Formula

    Posted 18 days ago
    Changed Rounded() to Ceil() and now it works!

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