NETWORKDAYS Formula using TODAY() not returning expected results

I'm using a Formula to calculate the pays past due from today. The current formula I'm using is =NETWORKDAYS([Due Date],TODAY()), but the results aren't coming out as I would have anticipated.

9/29/2021 returns 2 days past due

9/30/2021 returns 1 day past due

10/1/2021 returns -2 days past due

So, I created a formula that works in excel: =NETWORKDAYS([Due Date],TODAY())-(TODAY()>=[Due Date])+([Due Date]>=(TODAY()+1)) and this gives the results:

9/29/2021 returns 1 day past due

9/30/2021 returns 0 days past due

10/1/2021 returns -1 day past due

However, when I use the formula in SmartSheet, the returned result is #INVALID OPERATION.

Can someone help me figure out what the correct formula would be to get the same results I'm getting when I use that formula in excel?

Find more posts tagged with

Formulas

Accepted answers

MCorbin

Here you go:

=IF(Date@row <= TODAY(), NETWORKDAYS(Date@row, TODAY()) - 1, NETWORKDAYS(Date@row, TODAY()) + 1)

All comments

MCorbin

Try this:

=NETWORKDAYS(Date@row, TODAY()) - 1

MCorbin

Oops! Don't try that :-) It works for dates in the past, but not future dates. Working on it.... give me a minute

Jsmaldo2

So, I tried that too, and the result works for any days that were before today or the current day, but it makes the next day -3 rather than -1. I need some kind of formula that would subtract 1 from the current day and any days before the current day and add 1 to any days in the future. The formula I used in excel seems to do the trick, but when I use it SS I end up with the error. Struggling trying to figure out an equivalent formula.

MCorbin

Here you go:

=IF(Date@row <= TODAY(), NETWORKDAYS(Date@row, TODAY()) - 1, NETWORKDAYS(Date@row, TODAY()) + 1)

Jsmaldo2

That did it! Much appreciated!

Wina Hathaway

I need help as well:

if Date Submitted = blank then calculate number of days from Date to Vendor to today.

Thank you!

Wina

Genevieve P.

Hi @Wina Hathaway

Try something like this:

=IF([Date Submitted]@row = "", NETWORKDAYS([Date to Vendor]@row, TODAY()))

See: NETWORKDAYS Function

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.