I am trying to get the highlighted formula to exclude weekend days.

The way the sheet is currently setup, each row is a different job. there are no parent or child lines. I am trying to set a milestone date from one department task to another. However, the formula doesn't seem to recognize or account for when dates fall on a weekend. 1/6/24 and 1/7/24 are both weekend days. How can i write this formula to do that? Thanks,


Tags:

Answers

  • Try this

    = workday([Target Completion: Design}@row,[Fabric ordered milestone]@row

  • that did not seem to work.

    I get this error: "#incorrect argument set"

  • KPH
    KPH ✭✭✭✭✭✭

    Hi @Jason L.

    To correct the incorrect argument set error

    • The } in the formula should be a ]
    • The formula needs a ) at the very end

    However, I am concerned it might not work for you as your milestone column looks like it might be a duration column and the workday function needs a number - ie 1 rather than 1d. You may just see an invalid data type error instead.

    Have a try, and let us know what happens.

  • Jason L.
    Jason L. ✭✭
    edited 02/20/24

    I changed the all the "Milestone column" from a duration to a text/number column type. Then i tried adding workday to the formula like this: =WORKDAY([Target Completion: Design]@row + [Fabric ordered milestone]@row)

    I am now getting #INCORRECT ARGUMENT SET.

  • KPH
    KPH ✭✭✭✭✭✭

    Hi

    You have swapped the comma for a plus sign. Try

    =WORKDAY([Target Completion: Design]@row, [Fabric ordered milestone]@row)

  • I believe I figured out my own issue. After playing around with the formula, I got it to work. I don't fully understand why it worked but here is the new version of it:

    =WORKDAY([Target Completion: Design]@row, [Fabric ordered milestone]@row)

    I needed to add a "comma" after the first "@ROW" and then remove the "+" from between the date columns. It now is accounting for weekends and holidays. If anyone can explain why this works i would love to know. Thanks.


  • KPH
    KPH ✭✭✭✭✭✭

    Yes, somehow in correcting the issue with the wrong type of } and the missing ) you replaced the comma with a plus sign. The syntax for the WORKDAY function is the date then a comma then the number of days to add to that date.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!