Workday formula doesn't calculate correctly when value is "0"

pbartlett117 · 08/14/24

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.

Paul Newcome · 08/15/24

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.

Will.Parente · 08/15/24

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)]

pbartlett117 · 08/19/24

https://community.smartsheet.com/discussion/comment/449575#Comment_449575

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.

Workday formula doesn't calculate correctly when value is "0"

Answers

Help Article Resources

Categories