NETWORKDAYS formula give different answers with same inputs

If I use NETWORKDAYS formula in two cases mentioned below, I get different answers.

Date_T column has dates in text format (formulas mentioned in Formulas_T column). Networkdays formula gives 22.

=NETWORKDAYS(DATE(VALUE(MID([Date_T]1, 7, 2)), VALUE(MID([Date_T]1, 4, 2)), VALUE(LEFT([Date_T]1, 2))), DATE(VALUE(MID([Date_T]2, 7, 2)), VALUE(MID([Date_T]2, 4, 2)), VALUE(LEFT([Date_T]2, 2))))

Date_D column has dates in Date format. Networkdays formula gives 21.

=NETWORKDAYS([Date_D]1, [Date_D]2)

Any ideas what is going on?

Find more posts tagged with

Accepted answers

Jeff Reisman

@Adnan Mustafa

If you have Work Days set in your Admin Center, and there was holiday during January 2022, you would get one net work day fewer in your =NETWORKDAYS([Date_D]1, [Date_D]2) formula. This is because your embedded DATE functions evaluate out to dates in January 1922.

All comments

Jeff Reisman

@Adnan Mustafa

Adnan Mustafa

Thanks Jeff. Its the 2 digit year that was source of the problem. When I added '20' in the formula the days changed to 21.

I find it strange that year 22 defaults to 1922 and not 2022 in smartsheet.

Anyway, I was stuck here for many days. Thanks for the solution.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/321927#Comment_321927

Glad you got it working. The old "Y2K" thing is still coming back to haunt us 22 years later, LOL.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.