Combining 2 formula's to one for count of days between date ranges

Hi!

I am trying to calculate to get a the number of days between two date columns.

I have 2 date columns.

Date Reported

Date Resolved

There will ALWAYS be a date in "Date Reported", however, in "Date Resolved" the date could be blank (so I would want to count is as "current day".)

I have the formula to get the number of "days" between Date Reported and Date Resolved, when a date is populated in both fields, working.

I have the formula to get the number of "days" between Date Reported and Date Resolved, when a date is populated in Date Report, and Date Resolved is blank, working.

I am trying to combine these two formula's to be provide the correct # of days for either scenario above. I have tried "or" and "if count" (and a few others...)by reading posts here, however I get either parsing errors or invalid data type errors.

Here are my two formula's that I want to turn into one formula that I can use for column "Business Days Open"

Formula 1:

=NETWORKDAY([Date Reported]@row, [Date Resolved]@row) - returns # of days between 2 dates.

Formula 2:

=IF([Date Resolved]@row = "", TODAY() - [Date Reported]@row) - returns # of days between Date Reported and current date.

Any assistance would be greatly appreciated.

Thank you,

Jen

Screen Shot 2022-04-01 at 5.25.29 PM.png

Find more posts tagged with

Accepted answers

Paul Newcome

You are going to want to nest an IF statement within the NETWROKDAY function.

=NETWORKDAY([Date Reported]@row, IF([Date Resolved]@row <> "", [Date Resolved]@row, TODAY()))

All comments

Paul Newcome

You are going to want to nest an IF statement within the NETWROKDAY function.

=NETWORKDAY([Date Reported]@row, IF([Date Resolved]@row <> "", [Date Resolved]@row, TODAY()))

jal1172

Thank you!!! I tried the nested "if" statement, but obviously was not formatting it correctly. Thank you so much for you assistance!

Paul Newcome

Happy to help. 👍️

Joan B

Hello... using this formula, =NETWORKDAY([Date Reported]@row, IF([Date Resolved]@row <> "", [Date Resolved]@row, TODAY())), how would the formula be modified to ignore and not return invalid data type error, if "date reported" is blank?

Paul Newcome

@Joan B You can wrap the whole thing in an IFERROR.

=IFERROR(original formula, "")

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.