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

jal1172

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

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()))

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, "")