NETWORKDAYS formula

I am trying to calculate days open between the request date and today and not count weekends however I want the count to start at 0, not 1. I am using the below formula but for everything with today as the request date, it is showing -2. Once it moves to tomorrow, the days open goes back to 1. How do I get rid of the -2?

=IF(ISBLANK([Complete Date]@row), NETWORKDAYS([Request Date]@row, TODAY() - 1), NETWORKDAYS([Request Date]@row, [Complete Date]@row))

Find more posts tagged with

Formulas

networkdays

Accepted answers

KPH

Is this what you need:

If today is the requested date, the result should be 0.
If yesterday was the requested date you would like the result to be 1.
If tomorrow is the requested date, you would like the result to be -1.

Is that correct? You might just need to move a parenthesis in your formula.

NETWORKDAYS([Request Date]@row, TODAY())

Means the working days between Requested Date and Today. When Requested Date is today, that is 1.

Your formula:

NETWORKDAYS([Request Date]@row, TODAY() - 1)

Means the working days between Requested Date and Yesterday (the part in bold means todays date minus one). When Requested Date is today, that is -2.

My suggestion:

NETWORKDAYS([Request Date]@row, TODAY()) - 1

Means the working days between Requested Date and Today (the part in bold), minus 1. The minus 1 is after the networkdays calculation so 1 is subtracted from the output, not from the date input.

When Requested Date is today, that is 1-1=0.

I hope that is what you need, and it makes sense.

All comments

KPH

Is this what you need:

If today is the requested date, the result should be 0.
If yesterday was the requested date you would like the result to be 1.
If tomorrow is the requested date, you would like the result to be -1.

Is that correct? You might just need to move a parenthesis in your formula.

NETWORKDAYS([Request Date]@row, TODAY())

Means the working days between Requested Date and Today. When Requested Date is today, that is 1.

Your formula:

NETWORKDAYS([Request Date]@row, TODAY() - 1)

Means the working days between Requested Date and Yesterday (the part in bold means todays date minus one). When Requested Date is today, that is -2.

My suggestion:

NETWORKDAYS([Request Date]@row, TODAY()) - 1

Means the working days between Requested Date and Today (the part in bold), minus 1. The minus 1 is after the networkdays calculation so 1 is subtracted from the output, not from the date input.

When Requested Date is today, that is 1-1=0.

I hope that is what you need, and it makes sense.

Aimee Hatcher

That worked, thank you!

KPH

Excellent! Thanks for letting me know.

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.