Discussions

 View Only
  • 1.  Formula Advice(Adding a day to duration if > X)

    Posted 11-20-2020 14:13
    Hey Everyone, 

    Wanted to see if I could get some direction on what I'm trying to do. I have a formula that displays a duration on a calendar based on previous fields numeric values and start dates. I have 7 different departments that are showing duration on a calendar. All departments are accurate besides Plumbing. My issue is because Plumbing works 4 - 10s and all others work 5 - 8s, so I'm trying to figure out how to say if the duration given is greater than 4, then add one day, so that Plumbing still ends on the correct day. Right now because Fridays are included, it ends a day early. But it has to apply to every Friday the duration passes to add a day the next...

    Here is the Formula that I have so far, (Including my attempt at adding an extra day):

    If([HVAC Duration] > 0, ToDate(WeekdayAdd(ToWorkDate([Start(3)]), [HVAC Duration])),
    If([Plumbing Duration] > 0, ToDate(WeekdayAdd(ToWorkDate([Start(3)]), [Plumbing Duration])),
    If([Plumbing Duration] > 4, ToDate(WeekdayAdd(ToWorkDate([Start(3)]), [Plumbing Duration]+1)),
    If([Radiant Duration] > 0, ToDate(WeekdayAdd(ToWorkDate([Start(3)]), [Radiant Duration])),
    If([Snowmelt Duration] > 0, ToDate(WeekdayAdd(ToWorkDate([Start(3)]), [Snowmelt Duration])),
    If([Gas Pipe Duration] > 0, ToDate(WeekdayAdd(ToWorkDate([Start(3)]), [Gas Pipe Duration])),
    If([Engineering Duration] > 0, ToDate(WeekdayAdd(ToWorkDate([Start(3)]), [Engineering Duration])),
    If([Fixtures Duration] > 0, ToDate(WeekdayAdd(ToWorkDate([Start(3)]), [Fixtures Duration]))))))))))

    Any thoughts would be appreciated!

    Thanks,

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


  • 2.  RE: Formula Advice(Adding a day to duration if > X)

    Posted 11-20-2020 15:16
    Maybe a Case() Statement for this? I'm just not sure how to reformat this code to not effect the other if statements and only the Plumbing.

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



  • 3.  RE: Formula Advice(Adding a day to duration if > X)

    Posted 11-24-2020 11:26
    Edited by Austin K 11-24-2020 11:26
    The issue is likely in the order your lines are listed. The way Quick Base works with a formula is it takes the first thing that is true and runs with that while not bothering to read the rest of your formula.

    If([Plumbing Duration] > 0, ToDate(WeekdayAdd(ToWorkDate([Start(3)]), [Plumbing Duration])),
    If([Plumbing Duration] > 4, ToDate(WeekdayAdd(ToWorkDate([Start(3)]), [Plumbing Duration]+1))

    Looking at those 2 lines the only thing that would ever be true is the first line. Because plumbing duration above 0 is always true even if it is also above 4. Swap those 2 lines with the > 4 line on top and at least the plumbing part may start working. I did not test this but hopefully my explanation on how the formula works helps you fix this if my suggestion does not.