Formula to include weekends & Holidays in count but exclude weekends as a date for the result

I'm converting a file from Excel to Smartsheets for Quarterly, Semi-Annual, & Annual inspections. I want to set the first inspection date up and then have formulas in place to auto assign the remaining inspection dates for the next 2-3 years. I'm using WORKDAYS with an if formula which is providing a result but it's not calculating the way I need it to. For example, if my first Annual inspection is 2/25/2022, then my next one should be 2/25/2023 but that date falls on a weekend so I would want to schedule the inspection the 1st day following the weekend. The function I'm using is setting the next inspection date for 7/11/2023 which is 511 days. How do I get the weekends and Holidays to calculate in my total days but not be set as a date for inspection?

Below are the formulas I'm using in Smartsheets as well as Excel. (I know Excel formulas don't work the same in Smartsheets)

Smartsheets: =IFERROR(IF($Package@row = "Quarterly", WORKDAY([Start Date]@row, 90), IF($Package@row = "Semi-Annually", WORKDAY([Start Date]@row, 180), IF($Package@row = "Annually", WORKDAY([Start Date]@row, 365)))), 0)

I have not added a Holiday reference to my formula yet but will be

Excel: =WORKDAY(J2+IF($H2="Semi-Annually",180,IF($H2="Annually",365,IF($H2="Quarterly",90)))-1,1,'DATA VALIDATION'!$E4:$E43)

**J2 is my start date - H2 is my package type - Data Validation houses my Holidays to exclude

Find more posts tagged with

Formulas

Accepted answers

All comments

Genevieve P.

Hi @TashaD

The way I would personally do this is to directly add one day less than what you need (ex. 89 days versus 90), then add one WORKDAY so that you can skip holidays and weekends if that final day is on a holiday or weekend.

Ex:

=IF($Package@row = "Quarterly", WORKDAY([Start Date]@row + 89, 1)

Adding the number before the comma means that it will add the number of days including holidays and weekends, and then the 1 after the comma is the workday added. If you have holidays to exclude as well, you can reference these at the end of your WORKDAY function:

=IF($Package@row = "Quarterly", WORKDAY([Start Date]@row + 89, 1, Holidays:Holidays)

Let me know if this makes sense and works for you!

Cheers,

Genevieve

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.