Skip to main content
Question

Formula Advice(Adding a day to duration if > X)

  • November 20, 2020
  • 0 replies
  • 26 views

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
------------------------------
This topic has been closed for replies.

  • Author
  • Registered
  • November 20, 2020
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
------------------------------