Networkdays/Workdays Excluding Holidays and Exceptions

Hello, I am having multiple issues with my calculations for NETWORKDAYS and WORKDAYS working in Smartsheets. I entered default holidays into the Non-Working Days (holiday, exceptions) box in the Account Administration field assuming that once I set up the holiday/exception dates Smartsheet would deduct these dates from the calculation when using the NETWORKDAYS/WORKDAYS formula. But that is not happening nor is the formula deducting weekends despite me having Monday through Friday selected as working days.

I have repeatedly alternated between NETWORKDAYS/WORKDAYS to get the right calculations, but nothing worked. When using NETWORKDAY if the Result Date and Date Processed were the same day the calculation output was 1? And it should have been 0 for the same day. Eventually, using feedback from the community help board I have entered the following formula in the “Processing Variance” field =Workday([Result Date]@row,0) – Workday([Date Processed]@row,0) which is still not giving me the expected results.

Below I have included a chart showing the current calculation and my expected calculation. I need the calculation to do the following:

a. Count only workdays for Monday through Friday;

b. Exclude any holidays/exception dates; and

c. Same dates in the Result Date and Date Process fields should give a result of “0”

Can anyone provide a solution for this?

Find more posts tagged with

Formulas

Government

Reports

Automations

Accepted answers

Sandra Guzman

Hello @Linda Armstrong,

Using the formula below will yield a result of 17 days and not take into account weekends or holidays.

=[Date Processed]@row - [Result Date]@row

If your working days and holidays have all been selected, then you would want to use the following formula to calculate how many days excluding weekend and holidays. I prefer to use the if error functionality so that the field will be left blank if no dates have been entered.

=IFERROR(NETWORKDAYS([Result Date]@row, [Date Processed]@row), "")

You also mentioned that you want the same date to count as day 0 and not day 1. So you would just need to subtract 1 from your formula above and it should give you the results you are looking for.

=IFERROR(NETWORKDAYS([Result Date]@row, [Date Processed]@row) - 1, "")

All the best!

Sandra

All comments

Sandra Guzman

Hello @Linda Armstrong,

Using the formula below will yield a result of 17 days and not take into account weekends or holidays.

=[Date Processed]@row - [Result Date]@row

=IFERROR(NETWORKDAYS([Result Date]@row, [Date Processed]@row), "")

You also mentioned that you want the same date to count as day 0 and not day 1. So you would just need to subtract 1 from your formula above and it should give you the results you are looking for.

=IFERROR(NETWORKDAYS([Result Date]@row, [Date Processed]@row) - 1, "")

All the best!

Sandra

Linda Armstrong

@Sandra Guzman

Hello. This worked perfectly. Thank you so much for all of your help!

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.