exclude weekends and holidays from this formula

TroyB8828us

Hello, I'm trying to exclude weekends and holidays from "Task End Date". The current formula is in the screenshot. Once I select a "Task Launch Date", we want the "Task End Date" to auto populate and exclude weekends and holidays.

Sandy Drew

Hello TroyB8828us

Assuming your Task End Date is not tied to project dependency settings, I would recommend using the Workday function you have above, but for the duration you need to pull out just the value so it can calculate it properly. Then have a separate column for your Holidays that you can reference in your formula as well.

=WORKDAY([Task Launch Date]@row, VALUE(LEFT(Duration@row, 3)), Holidays$1:Holidays$10)

Hope that helps!

Paul Newcome

If you have the dependency settings turned on, you shouldn't need a formula at all. Simply putting in the start date and duration should populate the end date skipping over weekends. You can also set working days, non-working days, and holidays at the sheet level or the account level.

https://help.smartsheet.com/articles/516392-defining-working-non-working-holidays-on-a-project-sheet

.

charish

Have you tried using the workday function: https://help.smartsheet.com/function/workday?frame=0&nav=1 - you might want to play around with the holidays portion based on your need, but accounting for weekends can be solved for with the formula.

TroyB8828us

Yes, I've tried that but its not working for me.

TroyB8828us

I've tried this and its not working

Hollie Green

You can use Networkday formula to exclude weekends and holidays.

TroyB8828us

So i changed the formula(see first screenshot) and now I'm getting a #incorrrect argument set" error(see second screenshot).

Sandy Drew

Hello TroyB8828us

Assuming your Task End Date is not tied to project dependency settings, I would recommend using the Workday function you have above, but for the duration you need to pull out just the value so it can calculate it properly. Then have a separate column for your Holidays that you can reference in your formula as well.

=WORKDAY([Task Launch Date]@row, VALUE(LEFT(Duration@row, 3)), Holidays$1:Holidays$10)

Hope that helps!

TroyB8828us

Thank you, that worked. Do you have a reference for the "holidays", I'm not 100% on how to set that up.

Paul Newcome

If you have the dependency settings turned on, you shouldn't need a formula at all. Simply putting in the start date and duration should populate the end date skipping over weekends. You can also set working days, non-working days, and holidays at the sheet level or the account level.

https://help.smartsheet.com/articles/516392-defining-working-non-working-holidays-on-a-project-sheet

.

TroyB8828us