Workday formula doesn't calculate correctly when value is "0"
Hello All,
Having difficulty getting my formula to work properly when a column's value is "0". If the value is >0 it works fine. The formula is supposed to follow the WORKDAY variable and exclude weekends, which it does unless the value in the [Days to add] column is 0 at which point it seems to ignore the workday rule. Any thoughts on how to fix this?
The column in question in this example is the [Trim/Process] in the second row where the date should be the 15th instead of the 13th.
The formula I have is:
=IF([Histology Requested]@row = 0, "NA", IF(OR([Decal Start]@row = "NA", ISERROR([Decal Start]@row)), WORKDAY(Fixation@row, [Days to add]@row, {KAN Holidays Range 1}) + 1, WORKDAY([Decal Start]@row, [Days to add]@row, {KAN Holidays Range 1})))
If it helps: To summarize this sheet, everything builds off [Last NX]. The [Days to add] tells how many days are added to the [Decal End] column which populates from [Fixation] and then each column after that should advance one day after the next.
Answers

That's how I would expect it to work. You are adding zero work days. If you are adding zero days, your date would just stay the same. Then your formula adds one regular day (outside of the WORKDAY function) which pushes it to the next day regardless of whether it is a weekend or not.
I would suggest replacing [Days to Add]@row with an IF statement to say that if the days to add is zero and the week day of the "starting" date is a Friday, then add 1 instead of zero workdays.

What's your +1 doing in your embedded IF Statement?
IF(OR([Decal Start]@row = "NA", ISERROR([Decal Start]@row)), WORKDAY(Fixation@row, [Days to add]@row, {KAN Holidays Range 1}) + 1
It would appear that this would return the number 1, add it to the Fixation Date and end the function if Decal Start is NA or an Error.
So on your second row the formula is doing this:
=IF([Histology Requested]@row = 0, "NA", [Histology = 1; return false  look at clause 2]
IF(OR([Decal Start]@row = "NA", ISERROR([Decal Start]@row)), [Decal Start = NA; return true  look at clause 1]
WORKDAY(Fixation@row, [Days to add]@row, {KAN Holidays Range 1}) + 1) [7/12/24 + (0  (excluded days) +1) = 7/13/24)]

Somewhere along the way of troubleshooting that formula, I had to add the +1 so the formula would reflect the next day in other columns, I believe it was when the [days to add] was 0. It worked until I came across the issue of it not accounting for weekends.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 351 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 443 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!